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?