Pivot Table Calculated Field vs Calculated Item

Friends,

In my last articles I have discussed about How to create or insert Pivot Tables, Customizing Pivot Tables and Calculated Field in Pivot Table.

Now I hope you have noticed that there is another option in Formula tab under Option or Analyze menu as Calculated Item. Today I am going to discuss with you about Calculated Item and the difference between Calculated Field and Calculated Item.

Let take an example, in the below Pivot Table I need to add Saturday and Sunday as one day and then this value should come combined with the existing data, i.e., data related to Saturday and Sunday will come as it was, additionally a new item will appear as Sat+Sun with their total value. Here we can use Calculated Item option.

Its' very simple to use, you need to know where & how to use it otherwise you will find Calculated Item as Grayed colored inactive option.

Where to Use Calculated Item ?

Calculated Item is used in situations where we need another Item in the Row Label item field, i.e., as another item in the same column. The below picture will clear it.

How to Use Calculated Item ?

Place your cursor in any cell within Pivot Table but remember the column should be in Row Labels box, i.e., select any cell in the column where you want to add the new Item.

Calculated Item

Now go to Options click Formulas, from the drop down menu select or click Calculated Item.

A new window will appear as Insert Calculated Item in "***". Give a name to the new Item (in my case it is Sat+Sun). From the Fields section click on the Column Name and you will notice the Items are listed in the right side under Items head. In the formula box enter your formula (in my example it is =Sunday+Saturday). Click OK and you are done, you can now see a new Item in the Pivot Table.

Insert Calculated Item Input Box

That's it, the main difference between Calculated Field and Calculated Item is the first option will create an entire Field and the Calculated Item will create a Item in the Pivot Table.

Pivot Table Before entering Calculated Item

Pivot Table After entering Calculated Item

Hope you have understand the difference between the Calculated Field and Calculated Item and now you can easily use Calculated Field and Calculated Item.

Thanks for reading...