With a pivot table, you can quickly summarize data, and show the Sum or Count for thousands of records. For example, in the pivot table shown below, the weekly regional sales are shown.
Besides showing a basic sum or count for the data, you can use custom calculations, to show things like a running total, or the differences between items in a pivot field.
Right-click on a value cell in a pivot table, then click Show Values As, to see a list of custom calculations that you can use.
Calculate the Difference
One that I use frequently is the Difference From custom calculation, that subtracts one pivot field value from another, and shows the result.
Note: If you want to show the difference between pivot fields, instead of pivot items, you can create a calculated field.
In the pivot table below, a second copy of the Units field has been added to the pivot table, and it shows the difference from the sum of one week’s sales to the next.
Change the Summary Function
You can use different summary functions with a custom calculation — not just a Sum. In the example shown below, the Units field is added to the Values area twice.
- Both copies of the Units field are set to show the Count summary function.
- The second copy of the Units field is changed to a custom calculation for Difference From.
Custom Calculation Tips
If you’re using custom calculations, here are a few tips to make them more effective.
- To make the data easier to understand, you can change the heading from “Sum of Units” to “Units Change”.
- You can add another copy of the Units field to the pivot table, and show both the total sales and difference in weekly sales
- Experiment with the pivot table layout, to find the arrangement that will be easiest to read and understand.
- Remember that a custom calculation can only calculate on items within the same pivot field. If you want to show the difference between pivot fields, instead of pivot items, you can create a calculated field.
Watch the Difference From Video
To see the steps for creating a Difference From custom calculation, please watch this short video tutorial.
Download the Sample File
To test the Difference From custom calculation, you can download the sample file from my Contextures website: Custom Calculations
____________________