Displaying the percentage of the subtotal and grand total in PivotTable.

In this tip, we discuss how the percentage of the subtotal and grand total is calculated on the data in a PivotTable.

In the example below, we have compiled a PivotTable data from Access, with a Product Name (1), an OrderDate (2) and the subtotals (3) of the units sold per product and the grand total (4).

We want the percentage per date and per product but, we also want the subtotals of the amounts that are displayed, to be retained. So, we need an extra column (field) in the PivotTable.
We will now add a field called “Amount” to the “Values” field.
To add fields to our PivotTable, select a cell in it.
This opens the PivotTable Field List (5).
Click on the “Amount” field and drag it to the “Values” (6) area.

After the field is added, we get the following format.
The “Sum of the amount” (7) field and an additional column (8).

We should now change the display the name and value of the field .
Click on the arrow pointing downwards on the “Sum of the amount” (8) and select “Value Field Settings” (9).

In the subsequent dialog box, we change the name to “% of subtotal” (10), we click on the “Show values as” tab (11) and in the menu, we select “% of total” (12) and select “Product Name” (13) as a base field.

The customized column:

To display the percentage of the grand total, repeat steps 5 to 12 but, in step 10, we select a different name, for eg., “% of Grand total” and in step 12, we type “% of Grand total.”

What we learnt from this tip is that a field can be inserted, repeatedly, in a PivotTable.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats