Calculated item issue

[Mcstefan]

Hi, I have a Calculated item in a Pivot Table. ACT vs BUD % = IFERROR(( Actual- Budget )/Budget,0).
The result by month is correct but the overall result of -333% is not. It should be -33%.
I have two filters in this Pivot Table on the Time Period
- value filters Amount not equal to zero (I had to eliminate these since as they were created by the Calculated Item)
- unfiltered 2 months 2411 and 2412, as the Actuals are zero.
How could I get the right result of the % Variance for the Total year?

 

[RoryA]

Which version of Office do you have?

The calculated item total is, like the other totals, a sum of the individual items - it is not recalculated using the other two totals. Using either Power Pivot or Power Query (or both) is going to be more flexible and probably the best way to achieve what you want (it also won't create all the additional 0 values). Calculated items are generally best avoided if possible.

 

[Mcstefan]

Which version of Office do you have?

The calculated item total is, like the other totals, a sum of the individual items - it is not recalculated using the other two totals. Using either Power Pivot or Power Query (or both) is going to be more flexible and probably the best way to achieve what you want (it also won't create all the additional 0 values). Calculated items are generally best avoided if possible.

Hi @RoryA,
thanks for the advice.
I have Office 365.
Cristian.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Mcstefan]

Hi @RoryA,
thanks for the advice.
I have Office 365.
Cristian.

I think that I went to fast on this. I assumed that the Power Pivot option that I have would be to create Measures in DAX, which I did. However I am not able to place the following Measures in the Rows area of the Power Pivot. My goal is to have Actuals, Budget and Act vs Bud % by Row and to have Periods by Columns as per the snapshot.
My table data is structured like this Columns: Account (Service, Parts, etc), Version (Actual, Budget), Period (2401, 2402, etc), Amount.

Actuals Amount=CALCULATE(SUM(Revenue_DATA[Amount LC]), FILTER(Revenue_DATA, Revenue_DATA[Version] = "Actual"))
Budget Amount=CALCULATE(SUM(Revenue_DATA[Amount LC]), FILTER(Revenue_DATA, Revenue_DATA[Version] = "Budget"))
Act vs Bud %=([Actuals Amount] - [Budget Amount])/[Actuals Amount] *100

 

[RoryA]

You don't put measures in the row area, they go in the values area. You can drag the Σ Values button from the Columns area to the Rows area to get the measures to stack vertically.

 

[Mcstefan]

You don't put measures in the row area, they go in the values area. You can drag the Σ Values button from the Columns area to the Rows area to get the measures to stack vertically.

Genius!! Many thanks. here is how my Power Pivot looks like