Add a Calculated Item to Group Items in a Pivot Table
January 26, 2023 - by Bill Jelen

Problem: I’m working with the small data set shown here.

My company has three product lines. The Cocoa Beach plant manufactures ABC and DEF. The Marathon division manufactures XYZ. I have a pivot table that shows sales by product. Remember that the total of items sold is 127.

I’ve read that I can add a calculated item along the Product division to total ABC and DEF in order to get a total for the Cocoa Beach plant. I select Insert Calculated Item. In the Insert Calculated Item dialog, I define an item called Cocoa Beach, which is the total of ABC + DEF.

However, when I view the resulting pivot table, the total is now wrong. Instead of showing 127 items sold, the pivot table reports that the total is 158.

Strategy: Your problem is that the items made in Cocoa Beach are in the list twice, once as ABC and once as Cocoa Beach. The calculated pivot item is a strange concept in Excel. It is one of the least useful items. You should use extreme caution when trying to use a calculated pivot item.
You could use the Product dropdown and uncheck the ABC and DEF items.

The resulting pivot table shows the correct total of 127.

Alternate Strategy: Instead of trying to use a calculated pivot item, you can add a Plant column to the original data. You can then produce a report that shows both the plant location and the products made at the plant, and the total will be correct (127).

Calculated pivot items sound like they should be useful, but they are not. You should avoid using them.
This article is an excerpt from Power Excel With MrExcel