Pivot table calculated item question

[Stijn Van Looke]

I would like to add calculated items to a pivot table but I'm faing some issues..

Attached you'll find a simple example of the data.
I have a flat list containing a plan code, program, demand, reference (budget, Foreast1, Forecast2 and actuals), mandays, P&S (third party stuff) and the total cost.

The pivot table shows the sum of mandays by reference.
What I would like to add is calculated items to compare forecast 1 with the budget, forecast 2 to forecast 1, ...
So the calculated items are based on 2 variables (reference and column mandays).
Who can help me out in adding the calculated items??
Thanks in advance.

 

[Debraj]

Hi Van!

Welcome to the forum..

Can you please re-create the pivot table with expected output manually.. if posible also include the calculation..
and upload for us.. to make Calculated Item..

 

[Stijn Van Looke]

Hi Van!

Welcome to the forum..

Can you please re-create the pivot table with expected output manually.. if posible also include the calculation..
and upload for us.. to make Calculated Item..

File updatdand added to the initial request...

 

[Istiyak]

Hi,

Pfa as requested.

Regards
Istiyak

 

[Vishal Srivastava]

Hi VAN,

I would suggest you to build your pivot table in PowerPivot and then add calculate field as below:


1) For "Budget" Field, use:
=CALCULATE(SUM(Table1[Mandays]),FILTER(Table1,Table1[Reference]="Budget"))

2) For "Forecast1" Field, use:
=CALCULATE(SUM(Table1[Mandays]),FILTER(Table1,Table1[Reference]="Forecast1"))

3) For "Forecast2" Field, use:
=CALCULATE(SUM(Table1[Mandays]),FILTER(Table1,Table1[Reference]="Forecast2"))

4) For "Actuals" Field, use:
=CALCULATE(SUM(Table1[Mandays]),FILTER(Table1,Table1[Reference]="Actuals"))

And now use following measure to get desired results:

Forecast1-Budget =[Forecast1]-[Budget]
Forecast2-Forecast1=[Forecast2]-[Forecast1]

I have also attached the excel file for your clear understanding.

Hope It will help you!

Thanks & Regards,
CMA Vishal Srivastava