Intro

A customer recently asked for some help with adding a % of Total column for sales data without a pivot table (read about it with Excel and with ExcelWriter). But I wanted to see how the same table and column would be done with a pivot table. Enter Pitan, the Pivot Mage and here we go!

Solution

We start with a basic pivot table that already has the store names as row labels and the sales data as the data values. We want to add a column that shows each stores’s sales totals as a percentage of the sales total over all the stores.

1. Add a duplicate column of the values that you want to show the percentages for. In this case, it’s “Store Sales”, so there will be two columns of “Stores Sales”: one for displaying the raw values and one for calculating the percentages.

2. Display the field values as % of Column Total. This can be done two ways:

• Right click on the field > Show Values As > % of Column Total
• Use Value Field Settings to format the field
  1. Go to the field in the field view
  2. Select ‘Value Field Settings’ from the drop down
  3. Go to the Show Values As tab
  4. Select % of Column Total

3. (Optional) Rename the field as desired (from Value Field Settings)

4. Congratulations!

For more information about different field calculations, see Microsoft’s article on how to Calculate values in a PivotTable Report.

Follow

Related posts: