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.
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:
Sound good? Alright, let’s do this thing.
Let’s take a moment and look at the data source for the PivotTable:
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:
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:
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:
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.
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!
- Download the file: ParentPercent