1. Drilling Down into Visualizations

Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Developing Effective Visualizations

14 lessons , 4 exercises

Preview Course


In Power BI, fields can be arranged in hierarchies. In this lesson, we’ll see how this affects visualizations. We’ll see how to drill down into visualizations, and how to move up and down through hierarchies within a chart.



  • A hierarchy is an ordered combination of fields that can be placed in charts and analyzed as a single entity
  • We can modify the data shown on a chart by moving up and down through the levels if a hierarchy

Moving through Hierarchies

  • There are three main ways of moving down into a hierarchy
  • Drill Down: When drilling, selecting a single bar or observation filters the chart to show only data for the selected observation
  • Expand All: Moves down through the hierarchy, showing the detail for all levels in the hierarchy
  • Show Next Level: Moves down through the hierarchy but ignores any levels above the currently selected one


In the previous course, we looked at the most commonly used visualization tools in Power BI, as well as how to format and analyze these visuals.

In this course, we'll look at the remaining visual types, as well as some of the more advanced tools for altering and analyzing your visualizations. We'll use the same software sales data set, which is already loaded into our PBIX file.

In Power BI, we can combine several fields into an ordered hierarchy.

Instead of placing all the fields onto a chart individually, we can add the hierarchy and move through the fields interactively by drilling up and down.

We've actually already seen hierarchies in our use of the date field. Let's create a simple graph of revenue by date.

If we look at the access well, we can see our date field has automatically been broken down into a hierarchy of year, quarter, month, and day.

Our graph shows revenue by year since year's at the top of the hierarchy.

Since we only have one year of data, our chart just shows a single column. We can obtain greater insight from this graph by examining the various levels of the hierarchy. The three buttons at the top-left of the visual and the Drill Down button at the top-right allows to cycle through the different levels of our hierarchy.

We can also use the drill data tab on the ribbon.

We'll start by selecting the Drill Down button. We can now drill down into the hierarchy by simply clicking on bars in the visual. When we click on the bar for 2016, we move down to the next level of the hierarchy and are presented with four quarters of data for that year.

We'll select quarter three and we move to the month level.

As expected, we're now presented with the three months of the third quarter.

Finally, we'll select August and see the revenue for each day in August.

This drill down functionality lets you easily zoom in on a specific section of your data. We can move back up by clicking the up arrow at the top-left of the visual.

We'll now turn off the drill down function by clicking the down arrow again.

There are two other methods for moving through a hierarchy. The first is Expand All, indicated by the two branch down arrows.

When we select this button, we see all our data at lower levels of the hierarchy. In this case, we first see year and quarter.

If we click the button again, we add month, and then, finally, we add day.

As you can see, this method presents every quarter, month, and day, while drilling down only showed us the specific quarters or months we selected.

As a result, the chart has many more bars than we saw before.

Again, we'll drill back up to the top of the hierarchy.

The last method for moving through the hierarchy is Show Next Level, indicated by the two down arrows.

This reduces the hierarchy by one level.

If we select this button, we see our data by quarters.

You may notice that the year variable is not included this time.

This method ignores higher levels and only analyzes the current and lower levels of the hierarchy. In this case, it means that the year is ignored and only the quarter, month, and day levels are analyzed.

If we had data for multiple years, we would still only see four columns here. In this case, quarter one of each year would be combined into one bar.

To understand this concept, let's move down to the lowest level of the hierarchy. Here, we see our data is being shown by day of the month without considering which month, quarter, or year the data comes from.

For example, the total combined revenue for the first day of every month in our data set is just over $1,000,000. This could be useful if we're looking for consistent monthly trends.

As we can see, this doesn't seem to be the case in our data set.

As you can see, hierarchies can be used to drill down into your visualizations. This functionality offers a simple way of gaining insights into a number of levels from a single visual.

In the next lesson, we'll build on this knowledge and learn how to create our own hierarchies.

Developing Effective Visualizations


My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial