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.
- Power BI - Tools and Functionalities
- Power BI - Timeseries, Aggregation, and Filters
- Power BI - Maps, Scatterplots and Interactive BI Reports
- Converting Power Law Distribution to a Linear graph
- Power BI - Interactive BI Dashboards
- Interquartile Range and Quartile Deviation using NumPy and SciPy
- Need of Data Structures and Algorithms for Deep Learning and Machine Learning
- Supervised and Unsupervised learning
- Underfitting and Overfitting in Machine Learning
- Regression and Classification | Supervised Machine Learning
- Difference between Machine learning and Artificial Intelligence
- Machine Learning and Artificial Intelligence
- Python | How and where to apply Feature Scaling?
- Difference between Supervised and Unsupervised Learning
- Python | Tensorflow nn.relu() and nn.leaky_relu()
- Redundancy and Correlation in Data Mining
- ML | Mathematical explanation of RMSE and R-squared error
- Box plot and Histogram exploration on Iris data
- NLP | Chunking and chinking with RegEx
- NLP | Training a tokenizer and filtering stopwords in a sentence
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.