Results 1 to 4 of 4

Thread: Custom formulas in Pivot Table

  1. #1

    Custom formulas in Pivot Table



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

    In the Pivot Report the subtotal and total function works well except in
    calculating the % variance in an income statement. The report tools
    calculate the sum of each line % but what I want as a sub-total and grand total
    is the total actual dollar minus the total budget dollars divided by the total
    budget dollars for the total variance percentage. Not the column sub total
    and grand total. How do I put a custom formula in for Excel 2007?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Hi there, and welcome to the forum!

    You know, it's interesting that you should ask this question... I JUST published a video course this week that covers this topic. If you're interested it is currently on an introductory special... you can find out more about it here.

    To your question, what I would do is insert a new calculated field so that it gives me the variances for each line as well as the subtotals. To do that:
    • Go to PivotTable Tools --> Options
    • Select Fields, Items & Sets --> Calculated Field


    Set the values as follows:
    • Name: Budget
    • Formula: =(Actual-Budget)/Budget


    Now click Add, and you should be done.

    Hope it helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    You misunderstood, I have turned on Pivot Table subtotals and grand total and I don't like the subtotal for only the total variance. Excel takes the column total
    and not the sub total actual and sub total budget to calculate the overall subtotal for that group. I want to overwrite the Excel group
    subtotal with a custom subtotaling formula. Yet everything I have tried is refused by the program. How do I do it?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Okay, just to be clear here...

    Is the variance worked out by the PivotTable, or is in your source data?

    Is there any way that you can upload a sample (with falsified numbers) of what it looks like now, vs what you want it to look like? It doesn't have to be a PivotTable, just a range of hard data is fine. (Double click the Reply to Thread button to attach a workbook.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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