Sign in or start a free trial to avail of this feature.
1. Drilling Down into Visualizations
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.
Lesson Goal (00:21)
The goal of this lesson is to use the inbuilt date hierarchy to drill up and down between years, quarters, months, and days on a single chart.
Understanding Hierarchies (00:38)
Hierarchies are a method of combining fields so they can be analyzed as a single unit. For example, when we create a chart using a date field, Power BI automatically creates a date hierarchy consisting of year, quarter, month, and day fields. The chart initially shows data by year, but we can move through each level of the hierarchy interactively. There are three methods for doing this: drill down, expand all, and show next level. These can be accessed by selecting the icons at the top of a chart or using the Data/Drill tab on the ribbon.
Using Drill Down (01:20)
Drill down can be enabled by selecting the single down arrow at the top of a chart. Drill down lets us filter the data we see on a chart by selecting a data point. For example, if we have a chart showing revenue by year, selecting a year shows us revenue by quarter for the quarters in the selected year.
Similarly, selecting a quarter shows us revenue by month for the months in the selected quarter, and selecting a month shows us revenue by day for the days in the selected quarter. We can move back up the hierarchy by selecting the single up arrow at the top of the chart.
Using Expand All (02:24)
The Expand All function moves through a hierarchy, showing data for every level in the hierarchy. It’s accessed by selecting the two branched down arrows at the top of a chart. For example, if we have a chart showing revenue by year, expanding all will show us revenue by quarter for every quarter in the data set. Expanding all again shows data for every month in the data set and so on. This can produce a chart with a lot of data points, as no data is filtered out of the view.
Using Show Next Level (03:05)
Show Next Level is accessible by selecting the two down arrows at the top of a chart. This method reduces the hierarchy by one level. For example, on a chart of revenue by year, selecting show next level changes the chart to show us revenue by quarter. In this chart, the year will be ignored completely. For example, data for quarter 1 will include data from quarter 1 of every year in the data set added together.