+ Reply to Thread
Results 1 to 8 of 8

Adding Sub Calculations to Pivot Table

  1. #1
    Registered User
    Join Date
    11-24-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    28

    Adding Sub Calculations to Pivot Table

    Hello,

    I have a file that holds all of the salaries and bonuses per year for our employees. Management would like to see the % salary change every year as well as the bonus as a % of salary for each year.

    However, I do not know how or even if this will work since you cannot (as far as I know) add calculated fields based on values within a row (Pay Category) in a Pivot Table.

    I would do just a nicely formatted table (no Pivot) but they want to be able to filter by manager (and subordinates)

    I attached an example file.

    Numbers.png
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Adding Sub Calculations to Pivot Table

    Why not just have formulas to the right of the pivot table to show the calculations?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    11-24-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    28

    Re: Adding Sub Calculations to Pivot Table

    Yes, I asked this myself but they do not want to see it that way when physically printed.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Adding Sub Calculations to Pivot Table

    You can make the blank cells empty and you can use conditional formatting to make it look identical to the pivot, so they wouldn't know.
    Or you can use simple VBA to literally copy and paste formatting after dragging formulas down to the bottom of the pivot. I have done that previously.

  5. #5
    Registered User
    Join Date
    11-24-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    28

    Re: Adding Sub Calculations to Pivot Table

    Ha, I like your workaround.

    The biggest issue facing me is when they want to filter and see subtotals by manager, I even tried using a macro and advanced conditional formatting but then they want to add/delete rows sometimes and it only adds to the frustration.

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Adding Sub Calculations to Pivot Table

    the macro would do whatever the pivot does.

    I would have a formula copied and pasted to the bottom of the pivot, then copy and paste formatting across the entire range for all your copied down formulas. You could even copy and paste VALUES at the end so the user has no idea it is/was a formula.

    You would do a macro fired off an event, pivot update or whatever. so they never even click a button.

  7. #7
    Registered User
    Join Date
    11-24-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    28

    Re: Adding Sub Calculations to Pivot Table

    I am not sure I've ever seen that been done before. Is there a link you can send me to an example?

    Sorry to be a pain.

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Adding Sub Calculations to Pivot Table

    Ha, well I would send you one of my examples but I am on my phone waiting on a gym partner. o_O

    Essentially you figure out whatever formula you need to the right for the percentages and such and have them either stored in the macro or in row 1 or in a REFERENCE worksheet hidden in the background, wherever you prefer.

    So you have a macro automatically fire off when the pivot table is refreshed or updated using the EVENT (you will have to put the code in the worksheet area not in a general module).
    Once the macro fires off, you clear all the formulas/values to the right, leaving your headers you manually created (or you can store the headers with the formulas on your references worksheet)
    You count how many rows the pivot table has after update
    You drag the formulas down to that number of rows
    You copy and paste formatting from the pivot table
    Then you can copy and paste values if you want

    Then you end the sub. This allows filters and slicers to control all of it...

    This is likely the event you would want to use.
    https://msdn.microsoft.com/en-us/lib.../ff822105.aspx
    Last edited by mikeTRON; 05-13-2016 at 02:19 PM.

+ 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. Adding Rows of Data (And Calculations) to a Pivot Table
    By rjbinney in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-29-2013, 12:26 PM
  2. Replies: 5
    Last Post: 05-02-2012, 02:26 PM
  3. Excel 2007 : Pivot table calculations
    By interested in forum Excel General
    Replies: 11
    Last Post: 12-30-2011, 07:21 PM
  4. Pivot Table calculations
    By salexander in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2011, 02:26 PM
  5. get Pivot table calculations
    By jeamsu in forum Excel General
    Replies: 1
    Last Post: 07-06-2007, 02:05 PM
  6. get:Pivot Table Calculations
    By jim in forum Excel General
    Replies: 5
    Last Post: 07-10-2006, 06:19 PM
  7. Pivot table for calculations
    By Santiago Boeri in forum Excel General
    Replies: 1
    Last Post: 02-21-2006, 09:40 AM
  8. add:Pivot Table Calculations
    By PB in forum Excel General
    Replies: 2
    Last Post: 11-25-2005, 03:25 PM

Tags for this Thread

Bookmarks

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