1. Drilling Down into Visualizations

Overview

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.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

Transcript

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.

Our goal in this lesson, is to use the built-in date hierarchy to drill up and down between years, quarters, months, and days on a single chart.

We'll use the same software sales status set which is already loaded into the 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 axis wall, 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 the year is 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, allow us to cycle through the different levels of our hierarchy.

We can also use the Data/Drill 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 moved 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-branched 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 dataset, is just over $1 million.

This could be useful if we're looking for consistent monthly trends.

However, there are no obvious patterns in this dataset. 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.