PivotTable Percentage of Parent Total

This post demonstrates how to set up a PivotTable to show 100% on the subtotal lines when using the show values as a percentage of parent total option.

Overview

Before we begin, let’s review our objective. We want to summarize our data by region, and within each region, by rep. We also want to show each rep’s sales as a percentage of the company total and as a percentage of their region’s total. When we are done, we want the report to look a little something like this:

20140320a

Sound good? Alright, let’s do this thing.

How To

Let’s take a moment and look at the data source for the PivotTable:

20140320b

To create the PivotTable report, we begin by setting up the row and value fields. We use region and rep as the row fields, and amount as the value field. Here is what we have so far:

20140320c

Now, let’s tackle the percent of total column. We insert the amount field into the values area again, by dragging-and-dropping it, and then open the Value Field Settings dialog. From the Show Values As tab, we select % of Grand Total. These steps are illustrated in the animated image below:

20140320-a

Next, let’s tackle the percent of region column. This option is available beginning with Excel 2010. We need to insert the amount field again, by dragging-and-dropping, and then open the Value Field Settings dialog. On the Show Values As tab, we select % of Parent Total, and then define the base field as Region. These steps are illustrated below:

20140320-b

By setting the base field as region, we tell the PivotTable the denominator. The numerator of course is the sales total, and the denominator is the region total.

If, instead of choosing Percent of Parent Total, we chose Percent of Parent Row Total, the report would properly display the individual rep percentages, because the parent of a rep is its region. The region subtotal rows would show the percentage of their parent total, which is the report total. This option would not show 100% in the region rows as desired. By using the Percent of Parent Total option instead, we are able to identify a specific base field, which is applied to all column values. This shows 100% for the region subtotals as desired.

Conclusion

Thanks to Microsoft for continuing to develop one of the best Excel features…PivotTables!

If you have any other approaches or tips, please post a comment below….thanks!

Resources

This article was written by Jeff Lenning

31 comments:

  1. Scott M
    Reply

    Hi, I’m using MS Excel 2013, and the Show Values as “Percent of Parent Row Total” option doesn’t exist. Did Excel remove this option? I even downloaded your file “ParentPercent” and it appears that after I undo that % of parent subtotal, I lose the option to add it again.

    Thank you,
    Scott

    1. jefflenning Post author
      Reply

      Scott,

      To my knowledge, Microsoft hasn’t removed that option…and to be sure, I just created a new PT and confirm that the Percent of Parent Row Total is still there. I’m wondering if it may have something to do with which version of Excel you may be using? For example, the home or personal version?

      Let me know?

      Thanks
      Jeff

  2. Kenneth Kasajian
    Reply

    When you have something like “% of Parent Total” and the see the value in the grid, what if you wanted to say, “I only want to see the rows that are more 30%”?

    It seems like you when you have a sum and then show it as “% of parent row total”, you can’t filter on that aggregated value.

    1. Kurt LeBlanc
      Reply

      If you click just to the right of the top row and click

      home>sort & filter>filter

      it will add filters to each column in the table.

      This is a work-around and isn’t technically a Pivot Table feature.

  3. Ravi
    Reply

    Thank you very much Jeff, i have been trying to do this in SSAS Cube and it is very simple in Excel.

    1. jefflenning Post author
      Reply

      Welcome 🙂

  4. bender
    Reply

    Why not just use the % of Parent Column calculation in the dropdown?

    1. jefflenning Post author
      Reply

      Bender,

      Thanks for your comment…often the right-click drop-downs are a fast alternative to opening the Field Settings dialog. Although the % of Parent Column option won’t provide the desired result in this case, the Percent of Parent Total option would…thanks!

      Thanks,
      Jeff

  5. Rick
    Reply

    Is there a way to use the “show as a percentage of total” column in a subsequent calculation, i.e. calculated field? I need to use percentage of total sales in order to distribute a fixed amount of rebates. I can manually add in this formula but it would be great if there’s a way to use the % total column as a calculated field so I don’t have to update every time the pivot table data changes.

    1. Kurt LeBlanc
      Reply

      Rick,

      I think what you need is the GETPIVOTDATA() formula and you can create your % column with a formula that automatically updates.

      Thanks,
      Kurt

    2. Kurt LeBlanc
      Reply

      Rick,

      I think you are looking for the GETPIVOTDATA() formula to make your own % column.

      Thanks,
      Kurt

  6. Simon
    Reply

    Hi, when I press % of Grand Total I get 100% even though I do have several values …why is that?

    Thanks,

    Simon

    1. Kurt LeBlanc
      Reply

      Hey Simon

      Can you send me your spreadsheet to help you better? I’m having trouble picturing the problem…My email is kurleb10@gmail.com

      Thanks,
      Kurt LeBlanc

  7. Liz Donathan
    Reply

    Hi. I have Excel 2010. I see how to show values as % of Parent Row Total, but I’m not allowed to choose a base field. (the base field choices are in gray font and I’m unable to make a selection). I’d appreciate any guidance you can offer.
    thanks!

  8. Liz Donathan
    Reply

    I answered my own question. I had selected the wrong choice and after selecting % of Parent total per the instructions I’m in business. Thanks much!

    1. jefflenning Post author
      Reply

      Glad you got it! 🙂
      Thanks
      Jeff

  9. DS
    Reply

    Where is “% of Parent Total” in Excel for Mac 2011?

    thanks

    1. jefflenning Post author
      Reply

      I’m so sorry, but I don’t have a copy of Excel 2011 for Mac, so I’m unable to confirm this option is available and if so where it may be. Hopefully another reader may be able to post a comment to help us out.
      Thanks
      Jeff

      1. Dan
        Reply

        I am using Excel for Mac (Office 2016 version), and “% of Parent Total” is not present. It is, however, present in my copy of Excel 2010 for Windows. “% of Parent Total” may be one of the PowerPivot features that is supported on Windows but not Mac.

        1. Kurt LeBlanc
          Reply

          I’m really sorry Dan, but I don’t have Excel for Mac…Hopefully another reader has it and can help.

          Sorry to disappoint

  10. Adam
    Reply

    Thanks for the post. How would I subtract one of those columns from the other?

    I’m trying to calculate the difference between % Parent Row Total for two different columns (time periods).

    1. Kurt LeBlanc
      Reply

      Hey Adam,

      To analyze the results of a Pivot Table, you can use the GETPIVOTDATA() formula. It’s a bit tricky to learn what the right arguments you need are, but once you do, it’s really useful! Let me know if you need more help with it.

      Thanks,
      Kurt LeBlanc

  11. Jeff Baisch
    Reply

    Thank you for this information. I have been trying to figure out how to get the percentage in the Region, based on your dataset. Is there a way to have the Rep % to show the total for each Region, then instead of the Region Total show 100% have it show the % of the Grand Total?
    Midwest
    JUB 53.90%
    TPS 31.43%
    WMS 14.67%
    Midwest Total 24.8 %

    Without having a separate column?

    1. Kurt LeBlanc
      Reply

      Hey Jeff

      Yes you can:) It is another option in “Show Values As” called “% of Parent ROW Total”. It’ll take the percentage of the different categories of the whole category, then display each category as a percent of the whole table. It’s pretty amazing how much a Pivot Table can do and how simple it is!

      Let me know how that works out for you,
      Kurt LeBlanc

      1. Jeff Baisch
        Reply

        “% of Parent Row Total” converted all to 100%.

        I am tracking labor hours with 4 different departments with various job codes in each department. Here is what I need to see:
        Dept BOH BOH Total Company Total
        Dishwasher Expo Cook
        Hrs Hr% Hrs Hr% Hrs Hr% Hrs Hr%
        Store 1092 44.51 10.63% 34.58 8.26% 339.76 81.12% 418.85 33.41% 1,253.49 100%
        1573 143.51 27.06% 7.52 1.42% 379.29 71.52% 530.32 37.02% 1,432.42 100%

        If I use “% of Parent Total” Based on JobDesc I get the correct % for each job, but the Department % is 100%
        With “% of Rot Total”, the Department % is correct but the Job codes are wrong. For store 1092, the percents are 3.55%, 2.76%, and 27.11%.

        1. Kurt LeBlanc
          Reply

          Hey Jeff

          Hmmm…I think it may just be how you set up the format. Try the department in the row field, with the job code under it in the row field as well. The % of Parent Row Total should show each job as a % of its parent department and each department as a % of the total. Let me know if that helps you,

          Kurt LeBlanc

          1. Jeff Baisch

            Due to the layout requirement of the report, I cannot move the jobcodes down into the rows. I have 97 stores in 12 different regions which are down the rows in the report.

            Thank you for your help in trying to clear this up.

          2. Kurt LeBlanc

            Hey Jeff,

            What percentages do you want to see? Please tell me in general terms, like job codes as a % of the total. I can hopefully help you further that way.

            Kurt LeBlanc

  12. Sophie
    Reply

    Hi,

    Would you have any suggestion on how I can calculate the % of parent (total, row and column) if I have a MAC and this feature is unfortunately not available?

    Thank you
    Sophie

    1. Jeff Lenning Post author
      Reply

      I’m so sorry, but I don’t happen to have a copy of the Mac version handy. But hopefully someone can help out and post a comment.

  13. Sophie
    Reply

    Hi Jeff,

    ok thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *