How do I add a formula column to my pivot, formula uses grand total of another column

[johnlink]

Hi All

Am looking at creating a pivot table which calculates TIPS JAR distribution for staff working in a Tavern. Management have decided that the best method to do this is by total hours for each worker as a percentage of grand total hours of all workers in a period.

I have created the data table and now have the pivot table set up but in column D I have had to manually add on the PAYOUT calc fields which apply employee percentage to GRAND TOTAL HOURS (user input in H2 field). Problem my column D is not dynamic as staff numbers change each period and the column has to be adjusted to suit.

Is there any way I can add this column to the pivot table. I see that calculated fields can be added but can not access a formula which includes the GRAND TOTAL of another field for this?

Hopefully some pivot table gurus out there can let me know how easy it is to do this??

Thanks in advance
johnlink

 

[steve the fish]

A pivot isnt the best for this. Consider below table in A1:B4 with your tip amount in E1:

Name	Hours
s	10​
t	20​
f	30​

Then a formula such as =B2/SUM($B$2:$B$4)*$E$1 can be used in C2. You could then pivot that if you wanted.

 

[johnlink]

Thanks to "steve the fish" for this idea. I ended up following your suggestion and making all calcs in the source data and only at the final step migrated off to a pivot table report for printouts etc. Personally I have found the pivot table calculated fields to be rubbish. Cheers johnlink.