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!
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
- Go to the field in the field view
- Select ‘Value Field Settings’ from the drop down
- Go to the Show Values As tab
- Select % of Column Total
3. (Optional) Rename the field as desired (from Value Field Settings)
For more information about different field calculations, see Microsoft’s article on how to Calculate values in a PivotTable Report.