Power BI Blog: Creating Custom Hierarchies
3 September 2020
Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau looks at how to create custom hierarchies.
Last week, we talked about renaming hierarchies in Power BI. Therefore, it might be a good idea to review how to create a custom hierarchy.
To keep things simple, we are going to use the same dataset as we did last time. This week, we will look at another donut chart!
Above is a very simple donut chart, where we have the Total Sales broken down by Country. However, for the sake of this blog, let us say that we wish to create a hierarchy so that we can drill down from Country to Province and ultimately to City.
We can just drag the ‘Province Name’ and City fields into the Legend area:
Yes, this allows us to drill up and down, but if we want to use this hierarchy on another visualisation, we will have to drag the three fields over each time…
A better solution would be to create a hierarchy. To create a hierarchy, we can right-click on the field at the first level of the hierarchy and select the ‘New Hierarchy’ option. In this example, the first level would be Country.
This will create a new hierarchy, called ‘Country Hierarchy’. The next step is to select the field to be at the second level of the hierarchy and drag it into the ‘Country Hierarchy’.
We have one more field to add to our hierarchy: the City field. Let me take this opportunity to demonstrate an alternative method of adding to a hierarchy. This time, we will right-click on the City field and select the ‘Add to hierarchy’ option. We will then be prompted to select a hierarchy. In this case we only have one hierarchy to add to.
We can now use this custom hierarchy in the legend area for the donut visualisation or any other visualisation!
That’s it for this week! Check back next week for more on Power BI.
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.