Help with Pivot Table Formula based on subtotals

1. Help with Pivot Table Formula based on subtotals

I need to add a calculation to my pivot table that takes the subtotal of the dept hrs/employee total hrs and * 86.67 hrs. I am not sure how to do calculations in a pivot table based on subtotals and totals.

For example:
10-Employee Hrs Calc
Dept 1
2/21 4 (Tlt Dept1/Ttl 10-Employee)*86.67
2/22 8 (Tlt Dept1/Ttl 10-Employee)*86.67
Ttl Dept 1 12 (Tlt Dept1/Ttl 10-Employee)*86.67
Dept 2
2/21 4 (Tlt Dept2/Ttl 10-Employee)*86.67
2/23 8 (Tlt Dept1/Ttl 10-Employee)*86.67
Ttl Dept 2 12 (Tlt Dept1/Ttl 10-Employee)*86.67
Ttl 10-Employee 24

I have attached a sample Excel file.

2. Re: Help with Pivot Table Formula based on subtotals

Here's a screen shot of what I'm trying to accomplish. I want the calculation column embedded in the pivot table. Just not sure how to create the formula.

Attachment 664762

3. Re: Help with Pivot Table Formula based on subtotals

Do you have Power Pivot, and do you need the overall grand total to be the sum of the individual employee totals? If the answer is yes to both, then you can use three measures (assuming Table1 as your source):

TotalHours: =SUM([Hours])

as attached.

There are currently 1 users browsing this thread. (0 members and 1 guests)