Pivot with percentages

[PsyberFox]

Hi there,

I've got the following extract of my data:

Employee Qty Target Efficiency
026 60 495 12%
026 60 495 12%
026 60 495 12%
026 60 495 12%

The above target is a daily target, but the qty is what was recorded during
the day... to pivot this data, it must produce the following pivot:

Employee SumOfQty AverageOfTarget
026 240 495

Which is fine... BUT I have the following two problems:
1. When adding a calculated field to calculate the efficiency it
calculates 12% (60/495) and not 48%.
2. When I put the SumOfEfficiency in my pivot, it's correct per day (48%) -
is there a way to calculate the grand total as an average?

Pse help with either of the above two...

Thank you!

 

[Bernie Deitrick]

Put the Employee and Target into the row fields (since neither change), and the Qty and Efficiency
into the data field, both set to sum.

HTH,
Bernie
MS Excel MVP

 

[PsyberFox]

That's all fine thank you, but the grand total for the percentage
efficiency... is there a way to have this be the average?
Thx