+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    Bangkok, Thailand
    MS-Off Ver
    Office 365

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

    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
    Forum Expert
    Join Date
    The Netherlands
    MS-Off Ver
    Excel 2016

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

    52 viewer on your topic and no answer.

    This indicates your question is not clear to the forummembers.


    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power pivot or pivot table for connecting data and creating calculated fields
    By jaryszek in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 02-06-2019, 08:31 AM
  2. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  3. Replies: 3
    Last Post: 12-03-2017, 01:41 PM
  4. power pivot formulas aren't calculating
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-06-2016, 06:02 AM
  5. Replies: 2
    Last Post: 04-12-2015, 05:36 PM
  6. Calculating percent change in Excel Pivot Table
    By adjit in forum Excel General
    Replies: 0
    Last Post: 10-16-2014, 12:21 PM
  7. Power Pivot with multiple pivot charts using different pivot data
    By Paul-NYS in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-10-2013, 10:18 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1