Calculating Variances (Value and %) in Pivot (not using power pivot) - possible?

Brian McGhee

New member
Joined
May 12, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Office 365
I have financial data that I need to show variances against a number of dimensions. I need this in pivot so that I can use filters to create automatic views for different audiences (e.g. Divisions and Heads of Areas).


In the pivot there are roll ups, so example income as a level with sub level of a number of income types (e.g. recurring, project, etc).


I have actual data for "Month Actual", "Original Budget for the Month", "REvised Forecast for the Month". I then need calculated columns (to be in the same pivot table), that calculates values for: Actual - Budget, Actual - Forecast and then those value as a % change (so (Actual - Budget)/Budget and (Actual - Forecast)/Forecast.


I am struggling to get this to work on each level (so income and the lower levels) as well as to get the % to calculate properly.


Please advise if these is a solution for this in pivot or if only achievable in power pivot, and if so, how?









 
Back
Top