Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
A hierarchy allows you to group multiple levels into a single dimension:
An example of a hierarchy: Wol is a product, which belongs to the Owl animal and ultimately to the Bird species.
You can add fields in a hierarchy into a pivot table with a single click:
Rather than ticking each of Species, Animal and Product, you can just tick the Classification hierarchy to include all of its levels in the pivot table.
The result looks pretty and is easy to use:
You can click on the outline symbols to expand or collapse species, animals or products.
This blog shows how to create and use hierarchies, then considers the special case of parent-child hierarchies.
I'll use this page to show how to create and use the hierarchy shown in the previous part of this blog.
You can only create a hierarchy by combining columns within a single table, so the first step is to use the RELATED function to do this:
We need to bring these 3 columns into a single table (the Product one, since this is at the bottom of the hierarchy).
Here's the formula for the Animal calculated column (the Species one would be similar):
This will display the animal to which each product belongs.
The next step is to switch to diagram view and create a hierarchy:
It doesn't matter which level of the hierarchy you choose; right-click on one of Animal, Product or Species and choose to create a hierarchy based on it (here I've chosen to start with the field at the top of the hierarchy, the Species).
You can now rename the hierarchy created (I've called mine Classification, although biologically it's anything but sound):
Here we're about to rename the created hierarchy.
Now click on each of the other two columns and drag them into their correct position in the hierarchy:
Dragging the Animal column onto the hierarchy.
Note that you have to be careful where you position the mouse - the following position wouldn't work, because I've dragged the column too far down:
The difference is subtle, but if you released the mouse button now nothing would happen (the horizontal line fills the whole table, not just the hierarchy width).
Eventually you'll end up with the required hierarchy:
You might also like to hide the component columns from client tools, as here, so that the only thing a user can choose is the hierarchy.
You can tick a hierarchy to add it to a pivot table:
Ticking the hierarchy ... | ... displays it in the pivot table. |
It's common to create a hierarchy for viewing dates:
Dividing time into years, quarters and months.
For our model, the other obvious hierarchy is by quadrant, region, town, centre and store:
A hierarchy with five levels in it.
On the plus side, there's no doubt that hierarchies can make for tidy pivot tables:
A user can instantly choose to see multiple levels of detail, just by ticking the hierarchy boxes.
Pivot tables based on hierarchies also look impressive:
The pivot table for the fields shown above.
You can even base a slicer on a hierarchy, although (as I've just discovered) all this does is to create one slicer for each of its levels!
The reason I'm not a big fan of hierarchies is that they restrict a user's choices. For example, there's no way to hide the Product column in this pivot table:
If you don't want a user to be able to expand down to the product level - tough!
Compare this with a non-hierarchical solution:
A user can now choose exactly which levels of detail to display.
I don't even think the result looks any worse:
A pivot table based on the fields above - it's hard to tell it apart from the one using a Classification hierarchy.
Perhaps I'm missing the point, but it seems to this owl that hierarchies don't bring much to the party!
An exception to the above is when you have a parent-child relationship built into a single table, which is the subject of the final part of this blog.
This blog will show how to take a single table containing parent-child relationships, and turn it into a pivot table.
To follow this example, you'll need to import the tblStaff table from the MAM database into your model:
Give the table a friendly name Staff.
Also, you'll need to create a calculated column to give the full name of each member of staff:
Concatenate the first name of each staff member with a space, and then the last name.
Consider Richard Madeley (there are some odd people's names in the table ...):
We'll track back and see who this person reports to in the tree.
Richard Madeley has staff id number 7, and reports to the manager with staff id number 4 (Joseph Conrad). He in turn reports to the manager with staff id number 1 (Gladstone Small), who reports ... to no one.
An organogram for the MAM top-level managers.
What this blog will do is show how to represent this staff hierarchy in a pivot table.
The first thing we'll do is derive who reports to whom:
The PATH function conveniently extracts a pipe-delimited list of id numbers.
The function used is:
=PATH( [StaffId], [ManagerId] )
You can use the PATHLENGTH function to find how long each id string is:
We'll use this to fine-tune the pivot table towards the end of this blog.
I'm going to arbitrarily create the following roles within the MAM company:
Level | Role |
---|---|
1 | CEO |
2 | Director |
3 | Manager |
4 | Junior manager |
5 | Team leader |
6 | Minion |
You can pick out each role by using a function like this:
=PATHITEM([IdPath], RoleNumber,INTEGER)
where RoleNumber can equal any of the numbers 1 through to 6. For example, to get someone's director, use this formula:
=PATHITEM([IdPath], 2, INTEGER)
This would give the following (only the first 3 roles are shown here):
Richard Madeley has staff member 1 as his CEO, staff member 4 as his director and is a manager.
Now we know the id number at each level of hierarchy, we can use the LOOKUPVALUE function to return the relevant person's name. For example, for the director this would be:
=LOOKUPVALUE(
[StaffName],
[StaffId],
PATHITEM([IdPath],2,INTEGER)
)
That is, look up the staff name column from the table where the staff id number is the one returned by the PATHITEM function. This gives:
I've altered each of the 6 calculated columns so that they look up the name corresponding to the staff id number at the respective managerial level in the staff table.
You can now use the 6 columns to create a hierarchy:
I've created a hierarchy called Organogram (see the previous part of this blog for how to do this), which includes the 6 managerial levels.
You will probably also need to create relationships between the Transaction, Pos and Staff tables:
I've imported the StaffId column for the Pos table, so I can use this to link to the Staff table, to show who served which person.
You can now incorporate your Organogram hierarchy into a pivot table:
I've also hidden the CEO, Director, etc columns from client view, to make the pivot table field list look as clean as possible.
Here's what this would show:
Almost perfect! Just a shame about Alex Seddon and his ilk.
To get round the awkward fact that for some sales (such as those made by Alex Seddon above) there are no minions or even team leaders, you could amend each level formula to test for blanks. For example, here's what the formula would now be at level 6 (the Minion formula):
=IF (
ISBLANK ( PATHITEM ( [IdPath], 6, INTEGER ) ),
Staff[Team Leader],
LOOKUPVALUE (
[StaffName],
[StaffId], PATHITEM ( [IdPath], 6, INTEGER )
)
)
That is, if there is nothing at level 6 in the path, show the next level up in the hierarchy instead. If you change the formula for all 6 levels, this will give data for every single column of the table:
Every level now has a value, even though some are repeats.
Things have improved, but perhaps not much:
Alex Seddon now appears to report to himself!
To stop multiple Alex Seddons appearing, you can create two new measures as follows:
Measure | What it will show |
---|---|
CurrentDepth | For any pivot table cell, the current level of hierarchy (1 to 6). |
ValueToShow | Blank if we've drilled down too far, otherwise the total sales. |
Here's an expression you could use for the CurrentDepth measure:
CurrentDepth:=IF (
ISFILTERED ( Staff[Minion] ),
6,
IF (
ISFILTERED ( Staff[Team Leader] ),
5,
IF (
ISFILTERED ( Staff[Junior Manager] ),
4,
IF (
ISFILTERED ( Staff[Manager] ),
3,
IF ( ISFILTERED ( Staff[Director] ), 2, 1 )
)
)
)
)
Here's what this measure could look like when you create it:
The measure in the staff table (it uses the NumLevels calculated column that we referred to earlier).
Here's what the ValueToShow measure would look like:
If we're trying to show data below the maximum number of levels for this member of staff, show a blank instead.
Here's the formula for this measure:
ValueToShow:=IF (
staff[CurrentDepth] > max(Staff[NumLevels]),
BLANK (),
SUM('Transaction'[Quantity] )
)
Finally, you can choose to display the ValueToShow measure in your pivot table:
Show our squeaky new measure.
Finally, the perfect pivot table!
Alex Seddon only appears at the appropriate managerial level.
A final question: was it worth it? At the very least, you'll certainly have learnt a lot about DAX functions if you've followed me all the way to the end of this blog!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.