In addition to the regular items in a pivot table, you can also create calculated items, in one or more of the pivot fields.
In this pivot table, we’re summarizing data about insurance policies, with the number of new, cancelled, and existing policies in five regions.
Instead of showing all the data, we need to show the cancellation rate in the Northeast and the Southwest. To do this, we’ll add three calculated items, and those formulas will overlap in some of the cells. And that can lead to some problems!
Add Calculated Item for Cancellation Rate
First, we’ll hide the “New” status, and the “Central” region, by removing the check marks for those items in the field drop down lists.
Next, we’ll create a calculated item in the Status field, for cancellation rate:
- Click on one of the labels in the Status field, such as cell A6.
- On the Excel Ribbon, under PivotTable Tools, click the Options tab
- In the Calculations group, click Fields, Items & Sets, and click Calculated Item.
In the Insert Calculated Item dialog box:
- Type a name for the calculated item – CancelRate
- Enter the formula: = Cancel/( Cancel+ Existing)
- Click OK, to add the item.
In the pivot table, the CancelRate row will appear as zeros, so format those values as percentage, with one decimal place.
If you click on one of the cells in the CancelRate row, you’ll see the CancelRate formula that is used in the cell.
Add Calculated Item for Regions
Next, we’ll create calculated items for the Northeast and the Southwest, to show totals for the regions in those areas.
To create a calculated item for the Northeast:
- Click on one of the labels in the Region field, such as cell B4.
- On the Excel Ribbon, under PivotTable Tools, click the Options tab
- In the Calculations group, click Fields, Items & Sets, and click Calculated Item.
In the Insert Calculated Item dialog box:
- Type a name for the calculated item – Northeast
- Enter the formula: = North + East
- Click Add, to add the item, and keep the dialog box open.
To create a calculated item for the Southwest :
- Type a name for the next calculated item – Southwest
- Enter the formula: = South + West
- Click OK, to add the item, and close the dialog box.
In the pivot table, drag the Northeast label to the left, so it is beside the North region.
Incorrect Cancellation Rates
The Northeast and Southwest columns are showing totals for the Cancel and Existing values, and those numbers are correct.
However, the CancelRate item is also being summed, which is not what we want. For example, the Northeast CancelRate shows 11.7%, which is the total of 5.9% + 5.8%.
Instead, we want that rate calculated as it is in East: = Cancel/( Cancel+ Existing). The rate should be 5.8%.
If you click on the Northeast CancelRate cell, the Northeast formula is showing, instead of the CancelRate formula.
Change the Solve Order
To fix the problem, you can change the Solve Order for the calculated items:
- Select a cell in the pivot table, and then on the Ribbon, under PivotTable Tools, click the Options tab
- In the Calculations group, click Fields, Items & Sets, and click Solve Order.
The message at the bottom of the Calculated Item Solve Order dialog box explains that the last formula listed is the one that determines the cell’s value.
We’ll move CancelRate to the bottom, so its formula will be used in the CancelRate row.
- Click on the CancelRate item, and click the Move Down button, twice, to move it to the bottom of the list.
- Click Close
Note: When you change the Solve Order, it affects all calculated items in the pivot table.
The Correct Results
With the Solve Order changed, the percentages in the CancelRate row are now showing the correct values – 5.8% for the Northeast and 2.7% for the Southwest.
When you click on the Northeast CancelRate cell, the CancelRate formula is showing, so the solve order change has fixed the problem.
Download the Sample File
To download the Solve Order, please visit the Calculated Item page on my Contextures website.
Watch the Video
To see the steps for creating calculated items, and changing the solve order, please watch this short video.
__________