Calculating Variances (Value and Percent) in Pivot (not using power pivot) - is it possible?

Brian McGhee

New Member
Joined
May 12, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,043
Members
412,565
Latest member
roberttaekim
Top