Excel: Add a Calculated Item to Group Items in a Pivot Table

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

1. The initial data set.

   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.

   

2. You've sold 127 units.

   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.

   

3. Add a new item.

   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.

   

4. The total changes from 127 to 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.

   

5. The only way to make the total correct is to hide the items used in the calculated item.

   The resulting pivot table shows the correct total of 127.

   

6. Sales are back to 127, but you can't see the product details.

   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).

   

7. Adding plant info to the original data set solves the problem.

   Calculated pivot items sound like they should be useful, but they are not. You should avoid using them.