A client recently approached me with a problem. They had a Pivot Table that showed revenue per location…
However their boss wanted to see revenue per country…
That would have been easy if the source data contained a country column. Unfortunately it didn’t. So how did I do it? The answer lies in Excel’s Calculated Item feature.
I created 3 formulas to create 3 new rows (items) for the Pivot Table…
UK: London + Manchester + Glasgow
France: Paris + Marseille
Spain: Madrid + Barcelona
To create a Calculated Item…
- Ensure the cursor is in a cell in the left-most column of the Pivot Table
- Select Pivot Table Analyse > Fields, Items, Sets > Calculated Item
- Click on the Field name that contains the items to be combined (Store Location)
- Enter an appropriate name for the item (e.g. UK)
- Remove the 0 from the formula box (but leave the = there)
- Enter the formula (TIP: double click each item to add it to the Formula box and manually type the + between each one)
- Click OK when done
If you want to see how I did it, check out the video below…
Download a copy of the file(s) used in this video: https://share.zight.com/nOu42ApR