Calculated Field in Pivot Table 1

[jkupov]

I'm on Excel 2007 and want to insert a calculation into a pivot table. I have been running the calculation in a field outside of the pivot table, but I really need it to be a part of the table so I can sort by the calculated field.

Anyway, I'm working with customer service survey returns which are imported from our survey vendor's website into an excel spreadsheet. My pivot table contains the following column headers in this order:

A= EmpID
B= Completely Satisfied
C= Satisfied
D= Neither Satisfied nor Dissatisfied
E= Dissatisfied
F= Completely Dissatisfied
G= Total

The Rows contain the employee IDs of those who have received surveys and the values are a count of each time the employee receives a survey return under the appropriate column heading. The total is a count of the total survey returns for that employee. So it looks something like this:

|A| |B|C|D|E|F|G|
BJones;|4|2|1|0|1|8|

*To consolidate space I used the column letters rather than the full column names. See above for actual Column Names*

What I want to add is a column H which calculates the percentage of each employees total returns that are "Completely Satisfied".

So... I want to divide column A by the Grand Total for each employee in Column G. So in the example, BJones has a Completely Satisfied rate of 50% (4/8).

Any ideas?

 

[Gavona]

I think we need to understand your source data.
If it is normalised with a column for Name and a single column for Score then:
You can add the Score column to the data area a second time and configure it to display the Percentage of Row. You can then drag the Data button to the row area so that both the count of and the percentage of are on a single row. Finally hide the columns displaying the percentages for the other scores.

Gavin

 

[jkupov]

Thanks Gavin,

My source data has the following Column headers:

A= Date of Visit
B= Transaction ID
C= Overall Satisfaction
D= Satisfaction with wait time
E= Satisfaction With Sales Associate
F= Employee ID

Each Row contains the result for each category (i.e. Completely Satisfied, Satisfied, etc.). For the example I gave in my original post, I am working with the data in column E (Satisfaction With Sales Associate).

In my Pivot Table I have the following fields selected:

Row Labels: Employee ID
Column Labels: Satisfaction With Sales Associate
Values: Count of Transaction ID

Does this help?

 

[Gavona]

Yes, the approach I suggested should work. I am not familiar with 2007 so apologies if this is not fully there:

"Satisfaction with Sales Associate" should be in the Values area twice, both Count,
the second should have value field settings, show values as set to "% of row" (and change the Custom Name to "% of Satisfaction with SA")

Column Labels should be:
Sum of Values and TransactionID, in that order

Gavin

 

[jkupov]

Yes- it did. Thank you very much