In our last post, we explored how to use calculated fields to get customized fields and perform analysis with them. In this post, we will get introduced to a similar concept that is called “Calculated items” and deploy it in our analysis.

Essentially, a pivot table consists of “fields” and “items”. These two have a parent-child relation in between them. A pivot table can have many “fields” and each field may have multiple “items”.

For this case, we have following database that will be used throughout this example:

In this table, fields and items are as under:

Let’s take an example; we want to calculate the total sales for three items pencil and pen. The new calculated item can be called “writing instruments” that will be equal to:

Writing Instruments = Pen + Pencil + Pen Set

In order to calculate the new item, we need to first insert the Pivot Table. For that you need to go to Tab Insert>Pivot Table and you will have a blank pivot table with all already available fields.

For creating a calculated item, you need to drag and drop the respective field (from which you want to calculate new item) to either row or column labels. For our case, we will drop “item” to row label that will display following result:

Now keeping your cursor on the row labels, you will be able to see the Pivot Table Options > Calculation > Field, Item & Sets > Calculated Item. This will enable the following dialogue box:

The name should be entered in the field “name” (Writing Instrument) and the formula that we have already pointed (Pen+Pen Set+Pencil). The single quote in Pen Set is due to space in between the two words, single quote is to mark them as one. Once you press ok, the new field will be added to the “item” section under “Item” field (see the last entry in the right hand side). When you exit from the dialogue box, you will see the following pivot table:

It is clear that writing instruments sales is the sum of pen, pen set and pencil – means that the sum 27978.09 need to be adjusted for the new item introduced.

Here will se de-select all the variables included in the formula.

And the final pivot table will look like:

Conclusion:

“Calculated item” is a handy option when want to rearrange items within a field. It can make your analysis more meaningful and avoid need to adding new items manually. Please download the sample file to see how it works.