• Excel Book Excerpt

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

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Add a Calculated Item
to Group Items in a Pivot Table

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

LE10000936.jpg

Figure 897 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.

LE10000937.jpg

Figure 898 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.

LE10000938.jpg

Figure 899 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.

LE10000939.jpg

Figure 900 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.

LE10000940.jpg

Figure 901 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.

LE10000941.jpg

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

LE10000942.jpg

Figure 903 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy