Hierarchies provide a method of organizing data in a table to recognize that one value encompasses all of the values beneath it. One very common hierarchy is a date hierarchy, which is used to show data summarized by year, then all the values for the quarters, then the values for three months in each quarter and each month which at the lowest level includes all the values for dates. There are other hierarchies which may also exist in data, such as sales regions. A sales region could include countries which include states or provinces which include cities which include actual addresses. Because this is how data is categorized, visualizations need to reflect this organization by containing hierarches.

Creating a Data Hierarchy in Power BI

Finding where to create hierarchies is the hardest part of creating them in Power BI, especially if one has ever created hierarchies in Excel Power Pivot as they are not it the same place. Hierarchies are not in the Relationships data view, instead they are found in the Report view. Right clicking on the ellipse next to any field in a table displays a menu, and the second item on the menu is New hierarchy. Hierarchies can also be created by clicking and dragging a field on top of another field, which also will create a hierarchy. Once the hierarchy has been created, to add another field to the hierarchy, drag a new value on top of the value with the hierarchy icon. If the value added is not added to the location you want it, click on the ellipse next to the field named and move the field up or down as you wish.

Drill Through Reporting in Power BI

There are two ways to do drill through in Power BI, either by adding fields to the group section on a visualization or by adding a hierarchy as a field. If I create a date hierarchy and add the hierarchy to the axis of a bar chart visualization, the top Right and left corners will have arrows in them. Drilling down and back up occurs by selecting either the up and down Arrow keys, then clicking on a bar.

For example, if the down arrow in the left corner is selected, clicking on the bar for 2013 will show data for the 2013 quarters. Repeated clicking will provide data down to the month, then the days in that month. The double arrow buttons provide the ability to show the data in the next level slightly differently. Clicking on the left double arrow will drill down to the next level. Clicking on it would provide a bar visualization for 4 quarters with data from all years, then 12 months of the combined years then the days. The double arrow with the line connecting the arrow only works on the highest level of visualization. When the double arrow line button is selected initially, it will show the quarters listed for every year, then the months and years, then the day, month and year. I hope you found this post helpful in explaining some of the features natively included in Power BI. If you are interested in learning how to expand the visualization capabilities of Power BI by including R visuals, please attend my upcoming webinar.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur