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
    Posts
    1
    Articles
    0
    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
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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
  •