Results 1 to 2 of 2

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

  1. #1
    Neophyte Brian McGhee's Avatar
    Join Date
    May 2020
    Excel Version
    Office 365

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Also posted at Excel Forum

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts