+ Reply to Thread
Results 1 to 9 of 9

How to subtract two pivot table columns that already represent a percent of column total

  1. #1
    Registered User
    Join Date
    05-18-2020
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    10

    Question How to subtract two pivot table columns that already represent a percent of column total

    I have a two part question:

    I have a pivot table where I display gross sales by brand(columns) for customers(rows) as a percent of column total for different years. In this case, separate columns display percentages that sum to 100% per column. I want to show the growth over years so I'd ideally like to subtract the 2019 numbers from 2020( two separate columns). Normally, I'd use a calculated field to subtract 2020-2019 gross sales, but I want the new column to display the differences of percent of column total. When I do this as a calculated field, I can't keep the values as a percent of the column total. For example, if brand A had 25% of its sales with customer A in 2019, and 35% in 2020, I want my new column to display +10%. Is there a way to do this?

    I also want this pivot table to be adjustable to represent each cell as a percent of column total or a percent of row total. I already use a bunch of slicers so is there a way to include a slicer or drop down menu to filter between % of row and percent of column?

    Thanks
    Last edited by fpkid1; 05-18-2020 at 04:32 PM. Reason: Clarification

  2. #2
    Registered User
    Join Date
    05-18-2020
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    10

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    I tried adjusting the source data for the pivot to represent percent of total. However, I want percent of total for each brand individually, not total sales.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    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.

  4. #4
    Registered User
    Join Date
    05-18-2020
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    10

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    I have attached an example workbook
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    I changed the format of sheet 2 (all sales in 1 column).

    After that a pivot table.

    See the attached file.

  6. #6
    Registered User
    Join Date
    05-18-2020
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    10

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    Thanks for the response. However, I would ideally like to take: 'sum of 2020 sales'/column total - 'Sum of 2019 Sales'/Column total and create a new column with this information.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to subtract two pivot table columns that already represent a percent of column tot


  8. #8
    Registered User
    Join Date
    05-18-2020
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    10

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    Not exactly. Ideally it would display as attached. Here I manually inputted the data.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How to subtract two pivot table columns that already represent a percent of column tot

    With helpcolumns on the data sheet.

    Please reply if this is what you expected.

    The results are not the same.

+ 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. Grand Total for Pivot Table Value incorrect for Percent value
    By krunk in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-01-2018, 02:52 PM
  2. Percent of 2 columns in pivot table..Need Urgent Help
    By saikatray in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-31-2017, 08:25 AM
  3. Percent Running Total in Pivot Table
    By ChrisJones123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-27-2015, 03:43 PM
  4. Replies: 0
    Last Post: 10-15-2014, 02:38 PM
  5. Replies: 1
    Last Post: 05-19-2013, 06:40 PM
  6. Replies: 1
    Last Post: 01-18-2006, 03:15 PM
  7. How to Make Pivot Table Sub-Total to subtract
    By yfrolov in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-05-2005, 06:35 PM

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