Power BI – Drilling Down and Up in Hierarchies
Prerequisite: Tools and Functionalities
This article shows how to drill up and down so as to visualize the data through different levels in Power BI. Using drill-down and drill up on your data points, you can explore in-depth details about your data. When a visual has a hierarchy, you can drill down to reveal additional details.
What is Drilling?
Drilling enables you to navigate through different levels within the dimensional hierarchy of your data source. This allows you to review underlying data for a particular area, and move through the structure of your data source based on your informational needs.
Drilling can be performed in two ways:
- Drill up
- Drill down
The dataset used is of ‘Office Stock Supplies’. Refer to the dataset to follow along with the below-given sections of the article. You can download the dataset from here : Dataset
Before getting to understand how Drilling works, we need to create a bar chart.
Creating a Bar Chart:
To create a Bar Chart (stacked column chart), do the following steps :
Step 1 - Upload your dataset into your Power-BI Model. Step 2 - Drag columns [order date, Units] from Fields section. Step 3 - Click on the Stacked Column Chart in the Visualization Panel.
NOTE: Power BI drill down feature requires hierarchy. [ Example : Hierarchy of OrderDate ] (shown in above image)
Hierarchy Display Panel:
Drilling in Hierarchies:
Drilling down allows us to get a granular view of the data in the datasets and we use Drill up to get back the original data.
Step 1 - Click on the drill down icon to turn it ON. Step 2 - Click on any of the column bars (say column 2) to Drill it down. (This will show the data of that particular column only) Example : Year (2015) --> Quarter (2) --> Month (May) --> Day (Days in month of May only) [Shown in image below]
Advanced Drilling in Hierarchies – Switch to Next Level
In this, drilling is done on the entire data, rather than just a particular section of data.
Higher-level hierarchies are not preserved when switching to next level. In simple terms, on switching to the next level, say from year to quarter we cannot see which quarter belongs to which year.
As per the hierarchy of OrderDate [ Year –> Quarter –> Month –> Day ], on clicking this button, Power BI completely disregards which year we want to drill into, and shows all the data available in Quarterly format. See in the image given below, how data is shown after switching to each next level.
NOTE : You can use Drill Up to get back the original data every time.
Expand down one level:
Unlike the previous approach, here on expanding to the next level, the data of the previous level is preserved. (i.e. on switching to the next level, from year to quarter we can see which quarter belongs to which year.)
The information of the previous levels is preserved in the shown level.
These were the various hierarchies of data and its interpretation of using various techniques. Understanding the data at every level is a very important task when making large business decisions. Therefore, it is very important to understand such techniques while making reports to make a well-informed decision. For any doubts/queries, comment below.