In the last article we saw how to use Calculated Fields. Now we will explore Calculated Items. Most people have never noticed them for 20 years.
But WOW!! What a powerful feature…
Estimated reading time 10 min
Please read the previous article about Calculated Items before you read this article.
Contents
Recap
Each column is called a FIELD and data inside it is called ITEMS. New fields can be created by using a formula on existing fields. Most often this is done on numeric fields.
Now we will see how to create formulas based upon items. But first let us understand the need.
The Need
Often we create pivot tables which contain monthly analysis. Each column contains a month. The next step is to summarize monthly data further by combining them into QUARTERS. Let us say, Jan + Feb + Mar = Q1.
You will be tempted to add a column after March and add a manual calculation. Unfortunately, Pivot Table does not allow you to add any columns within itself.
We then try to find our own method – copy paste the pivot table as values and then do the calculations. The “advantage” is that it allows you to do any kind of manipulation. But the big DISADVANTAGE is that all the benefits of Pivot Table are lost. You are now responsible to do these calculations and maintain them when more data is added periodically. This means more manual copy paste values and more struggle.
This is extremely time consuming and error prone.
DO NOT DO SUCH CALCULATIONS MANUALLY.
Pivot Table can do these for you very easily.
Download Sample File
Create a pivot table with Segment in ROW area, Months in COLUMN area and Amount in DATA area.
Now we want a new column called Quarter 1 which is the sum of Jan Feb and March.
Calculated Items
Remember, MONTH is the FIELD and individual months are the ITEMS. Also remember that the data in the month column is of TEXT type.
We are not adding Jan to Feb. We are adding the AMOUNT in Jan to AMOUNT in Feb.
Now click Pivot Table Tools – Analyze – Fields, Items and Sets and
choose Calculated Item. Unfortunately it is inactive (grayed out). Why?
Because your cursor is NOT in any ITEM. Here our cursor is in data area.
No problem. Click on any item (any month name in this case) and go to the same dropdown again. The Calculated Item option is now available.
Adding the Formula
The concept is similar to Calculated Fields. Here we see two lists – one for fields and one for items (all months). Double click on Jan then add the plus sign, double click on Feb, add the plus sign and double click on Mar. Give a name Quarter 1 in this case. Click Ok.
Unfortunately, the Quarter 1 column is added to the end – just before Grand Total. No problem. Click in the heading of Quarter one. Move the mouse around till it becomes a drag cursor (four headed arrows). Drag it to position it just after March.
Now we got what we wanted. Simple and Easy. Right?
Add three more Calculated Items for the remaining three quarters.
Do we really need to show the individual months? If not, just filter them out and only keep the quarters. That makes the report easier to view and interpret.
Another example : H1 and H2
Let us go one step further. Add two more Calculated Items for H1 and H2.
H1 is Q1 + Q2 and H2 is Q3 + Q4.
Now you can choose which level you want to show. Months, Quarters and Halves or any combination which you need. Full flexibility.
Use Show Values As and Conditional Formatting
We saw the power and flexibility of all options in Show Values As and Conditional Formatting. Use it to look at the data from different points of view. Don’t get stuck to one type of analysis. Try all relevant options and learn maximum possible things about the data.
Order of calculation matters
As one calculated field may be used in another calculation, it builds a hierarchy of execution. This order is shown using the Solver Order option.
You can change the order if formulas are not getting calculated as expected.
Document all the calculated fields and items
List formulas option adds a new sheet with all fields and formulas.
Could we not use Pivot Table Grouping for this?
Yes absolutely. We will cover it in a separate article.
But for those who know Grouping – it implicitly means summation. The Calculated Item approach is more flexible – we can use any mathematical operation and function.
Grouping approach becomes tempting if you have a simple Date column. It is easy to group it by Month, Quarter and Years. But once this is done, calculated items will be disabled.
Also remember that Grouping is not available in Pivot Tables created using Power Pivot.
Another example
Here is an example where the difference between Preferred and Non-Preferred customers is calculated using Calculated Items formula. (This could not have been done using Grouping).
Grand total for rows is invalid
In this case the Grand total for each segment is a total of Preferred, Non-Preferred AND the Difference. Therefore, with calculated items, you cannot look at Grand Total to get the actual idea of the REAL total. It is a good idea to create another Calculated Item which sums all items to get the real grand total.
Practice it and incorporate it into your reports
Calculated Fields and Items require some practice to understand and implement. Try these with your real data. Try it on a copy. Compare them with manually calculated results. When you are well versed with it, replace the manual formulas with relevant Pivot Table Calculations. Enjoy!
What Next
We will see Grouping in Pivot Tables in the next set of articles.
Articles in this series
Adding formulas OUTSIDE Pivot Tables = Inefficiency
Pivot Table – Show Values As – Part 2
Pivot : Show Values As – Part 3
Pivot: Running Totals and Ranking
Adding calculations WITHIN Pivot Tables
The amazing Calculated Items in Pivot Tables (This article)
***
