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