This Mousebyte explains how to create a calculated item for a field within a PivotTable. A calculated item is a formula within field.
It also explains how to change the solve order for calculated items where a cell has two or more possible calculations that it can perform.
We hope you find this Mousebyte tasty and wholesome!
We recommend in house Excel Training with Blue Pecan Computer Training.
What Exactly is a Calculated Item?
Calculated items perform calculations on items within a single field. For example, in the PivotTable below a calculated item would be able to calculate what percentage bakeware and cookware sales are of total sales. Each row label is an item within the field: bakeware is an item, cookware is an item and so on.
The calculated item essentially creates another row within your data where the calculation is performed – see below.
Don’t get calculated items confused with calculated fields. When you create a calculated field you are performing the same calculation on all items within a field or fields. You are essentially creating another column in your data. For example, if you wanted to calculate profit in a PivotTable you might create a calculated field that subtracted a Cost field from a Revenue field.
Limitations on the Use of Calculated Items
- You cannot perform average, standard deviations or variance calculations in a PivotTable containing calculated items;
- Fields containing calculated items cannot be moved to the Filters area of a PivotTable;
- You cannot use grouped fields in a PivotTable containing a calculated item;
- A calculated field can only refer to items in the field it is being created in.
Creating Calculated Items
To create a calculated field, start by clicking in a row or column heading. Here we want to create a calculated item for our product categories which are row headings, so we would select one of the cells containing a row label (product category).
Now click on the Ribbon’s Analyze tab (or Options tab in earlier version of Excel). Click the Fields, Items & Sets button and in the menu click Calculated Items.
The Insert Calculated Item dialog appears. In the Fields list make sure you have the correct field selected – the field you want to create the calculation within.
You are now ready to create your formula. Use the Items list to add items to your formula – either double click on item to add it to the formula box or select it and click Insert item. We are going to calculate what percentage the combined sales of bakeware and cookware are of the total sales. The formula would be:
= (BAKEWARE+ COOKWARE)/(BAKEWARE+ COOKWARE+ CUTLERY+ DRINKS)
The normal rules of precedence apply to these calculations, so don’t forget your brackets where needed. NOTE: You can also use a worksheet functions such as IF or AVERAGE in a calculated item but you can’t refer to a worksheet cell or a named range anywhere in the formula.
Once you have completed your formula, give the field a useful name. If you are only creating the one calculated item click on OK to confirm. If you intend to create more, click on Add and carry on from there.
The calculated field will appear at the bottom of the PivotTable. You can always drag it to a different position if preferred.
Apply formatting to the calculation if needed. Our calculation needs to be formatted as a percentage value. NOTE: if you format the PivotTable field then all values will be formatted – so you end up having to format the cell instead.
Next we are going to add the region field to the PivotTable – this will enable us to analyse sales by region.
We need to summarise sales within England as a single region, in other words add up MID, NOR, SOU and SWEST. We can then compare the whole of England’s sales to the sales in Scotland, Ireland and Wales.
Start by clicking in a region column heading and then click on the Ribbon’s Analyze tab (or Options tab in earlier version of Excel). Click the Fields, Items & Sets button and in the menu click Calculated Items.
The formula this time is =MID+ NOR+ SOU+ SWEST. Click OK to confirm.
The calculated item appears on the right of the PivotTable. We can now filter out the individual English regions.
Click on the Column Labels filter button and untick the regions that you want to hide. Click OK to confirm.
At this stage you may notice a bit of a problem with the BW & CW Share of Sales calculation for the new ENG region.
If you select that cell and look in the formula bar you will see it is performing the wrong calculation.
This cell is on the intersection of a calculated row and column. By default, in this situation Excel will always use the calculated item most recently created.
Changing the Solve Order of Calculated Items
Where a cell is using the wrong calculation, you need to use the Solve Order command to change the calculation. Select the cell in question and then on the Ribbon’s Analyze tab (or Options tab in older versions), click Field, Items & Sets. In the menu select Solve Order.
In the Calculated Item Solve Order dialog move the calculation that you want to perform to the bottom of the list. The calculation at the bottom of the list is always applied. You can use the Move Down button to achieve this. Click on Close to confirm.
The correct calculation is now applied.
Refer to Items Using an Index Number
In the example below the calculated item formula refers to the position of the date items rather than the date items themselves.
If you filter your date row labels, the calculated item will always add up the first two dates returned by the filter, giving your calculation a lot more flexibility. Also, if you refresh your data source with say next month’s data, again because the formula is not looking for specific dates, it will still perform the necessary calculation.
You can also refer to an item’s position relative to the position of the calculated item. To refer to the to the last two dates…
… you would create the following formula.
Positive numbers refer to items below or to the right. Negative numbers refer to items above or to the left.
Modify Calculated Items
To modify a calculated item, click on any field row or column heading in your PivotTable and on the Ribbon’s Analyze tab (or Options tab in earlier version of Excel). Click the Fields, Items & Sets button and in the menu click Calculated Items.
In the Name drop down select the Calculated Item you want to modify. Make changes to the formula and then click the Modify button. Click OK to confirm.
Remove Calculated Items
To remove a calculated item, click on any field row or column heading in your PivotTable and on the Ribbon’s Analyze tab (or Options tab in earlier version of Excel). Click the Fields, Items & Sets button and in the menu click Calculated Items.
In the Name drop down select the Calculated Item you want to delete. Now click the Delete button. Click OK to confirm.