Problem: I'm working with the small data set shown here.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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.