Help with Pivot Table Formula based on subtotals and totals

[lisa6538]

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've attached a sample file

 

[lisa6538]

Here's a screen shot of what I'm trying to accomplish - see calculation column. I want this embedded in the pivot table.

 

[GraH - Guido]

You can "easily" accomplish this through a Power Pivot (data model) and a set of DAX measures.
Question is however if you are up to it and if your excel version has it on board. What is your excel version? 2010 or above will do.
I don't have time left this morning, but I can give a go later.

 

[rahulshewale1]

HI @Lisa P ,


See if it ok ?

Powerpivot Solution

@GraH - Guido Suggested you can download PowerPivot

Regard
Rahul shewale

 

[lisa6538]

@rahulshewale1 I do have Power Pivot, I also have Kutools. I would love more information how you created the calculation within the Pivot Table. The workbook you attached is exactly what I was looking for.

 

[GraH - Guido]

In the powerpivot ribbon look for manage measures and reveal the DAX formula. (Then start learning DAX )

 

[rahulshewale1]

Hi @lisa6538 ,

Please follow below step to achieve result.

1. Load the data into table ( Ctrl + T )
2. Go to Power Pivot Ribbon >Click Add Data Model
3. Type Formula in Messure grid >> Total Hours:=SUM(Data[Hours])
Calculation:=DIVIDE([Total Hours],CALCULATE([Total Hours],ALL(Data),VALUES(Data[Employee Id])),BLANK())*86.67
4. Pivot table Option > Choose Pivot Location > OK > Drag Field > Formula in Value Area


Regard
Rahul Shewale

 

[p45cal]

There is a way to do it in a standard Pivot table by adding a column to the source data (Table1).
In the attached I've added such a column (I kept the default name Excel gave it but you can of course change it) then refresh your pivot table and add the new field to the values area.

The formula for the new column in the source data is:
=86.67*[@Hours]/SUMIF([Employee Id],[@[Employee Id]],[Hours])