What is Drill Up in Power BI?
Drill Up in Power BI is a feature that allows users to create data visualizations at different data granularity levels, i.e., from the lowest level to the highest level in your visualization. Drill Up requires your data to be in a hierarchical structure and helps you visualize each data hierarchy level. For example, if you have a sales dashboard with multiple hierarchies such as Product Segment and Product Category, using the Drill Up feature, you can visualize your data at the Product Category and then move one level up to view your report at the Product Segment.
Drill Up is one of the Drill modes that Power BI offers for analysis of hierarchical data structures. Depending on how you have defined the hierarchy structure in your data, drill up will move you to one level up till you reach the highest level of hierarchy structure and hence can be quite convenient from a user perspective to use drill the hierarchy-based datasets.
Table of contents
Key Takeaways
- Drill Up in Power BI enables users to navigate the hierarchical data from the lowest level to the highest level in your visualization.
- It helps you to visualize each data hierarchy level and explore the detailed information as applicable to that level.
- You can define the levels of hierarchy for your dataset and apply the Drill up feature to the dataset.
- Consider using the Power BI interaction with the Drill Up feature to enhance your data analysis experience and derive data insights in Power BI.
- You can customize the Drill Up feature such as enabling/disabling the Drill Up option and accessing it either through the action bar or by right-clicking the visual.
How to use the Drill Up Option in Power BI?
Before you start using the Drill Up option, there is a pre-requisite you need to fulfill. These include:
- Power BI Pro or Premium license
- Data in a hierarchy structure
To use the Drill Up option in Power BI, follow the instructions outlined below:
Step 1: Open your Power BI Desktop on your machine.
Step 2: Import your dataset into Power BI Desktop using the Get data option and Load the dataset using the Load option.
Step 3: Navigate to the Visualizations pane and select a visual icon from the available list.
Step 4: Drag and drop the hierarchy data fields from the Data pane to the Visualizations pane to map the sections. Here we have defined the hierarchy structure as Year – Product – Segment.
This will create a data visualization in Report Canvas in your Power BI Desktop.
Step 5: Hover your mouse over the visual. You will view the action bar menu with different visual icons in the report canvas.
Alternatively, you can also right-click on the visual. This will display the Drill modes in Power BI.
Step 6: Select the Power BI Drill up button as per one of the ways highlighted in the above step.
Step 7: As you can see, the hierarchy structure has moved from Product to Segment. The Power BI Drill up button icon in the action bar is now disabled as the visual is at the highest hierarchy level i.e. Segment.
Note: The Action bar contains the Power BI drill up and drill down button icons along with other
Step 8: Hover your mouse over the data visualization and click on Expand all down one level in the hierarchy icon.
This will move the visual to the next level down in the hierarchy. You can also view the Power BI Drill up button is enabled now.
Step 9: Repeat the same step as mentioned in the previous step. This will move the visual to the lowest level of the hierarchy structure.
Step 10: Click on the Power BI Drill up button to move from the lowest level to one level up till you reach the highest level of the hierarchy.
Note: You can perform Power BI Drill up average by creating calculated columns or measures for your visuals. This will help you to visualize the data points with changing granularity.
You can’t perform Power BI drill up all visuals as only specific visuals support drill modes. Make sure the visuals you are using support Power BI drill up and drill down features in Power BI.
Examples
In this section, we will go through a few examples of using the Drill Up option in Power BI Desktop.
Example #1
In this example, we will demo the use of the Drill Up option in Power BI by creating a simple Cluster column excel chart using the laptop Price dataset. The laptop Price dataset contains a collection of laptop product listings scraped from an online e-commerce website.
To use the Drill Up option in Power BI Desktop, follow the instructions below:
Step 1: Import laptopData data into Power BI using Get data and load into the Power BI Desktop data model.
Step 2: Create a data visualization in Report Canvas using the data fields.
Here we have created a clustered column chart using the data fields from the Fields pane. The X-axis represents the hierarchy of the dataset i.e. Ram – OpSys – TypeName – Company
Additionally, we have added slicers in Power BI to provide user interactivity for data filters.
Step 3: Apply formatting to the data visual to make it visually appealing for the end users.
Once the changes are applied, you will be able to view the data visualization in the report canvas.
Step 4: Hover your mouse over the Stacked column chart. You will see an action bar on the right corner of the visual.
Step 5: Click on the Drill Up arrow icon from the Action bar.
This will move the visual to one level up i.e. from Ram to OpSys.
Step 6: Click again on the Drill Up arrow icon from the Action bar. You will move the visualization one level up from OpSys to TypeName.
Step 7: Repeat the same process as highlighted in the above step. The data visualization will move from TypeName to Company level.
Note that the Drill Up icon is now disabled for further usage as we have defined the Company level as the highest granularity. You can use the Drill down feature to move one level down i.e. from Company to TypeName granularity.
Example #2
In this example, we will demo another example of using the Drill Up option with multiple data visuals. For this, we will use Starbucks data that contains the Starbucks Beverage Components such as nutrition info for Starbucks menu items.
To use the Drill Up option in Power BI Desktop, follow the instructions below:
Step 1: Load the Starbucks data into the Power BI using the Get data option.
Step 2: Navigate to the Visualizations pane and select the visual icons. Drag and drop the data fields from the Data pane to create data visualizations.
Here we have created 3 visualizations, i.e., Clustered Column chart, a Line and Stacked Column chart, and a Donut chart.
Here, for the Clustered Column chart, we have defined the hierarchy structure as Beverage_prep – Beverage – Beverage_category.
Step 3: Apply formatting options to your data visualization, i.e., Title, visual fonts, and data labels. This will create a data visualization in the report canvas.
Step 4: Hover your mouse over the chart visual. This will display the Action bar containing the Power BI drill up and drill down and other drill modes.
Click on the Drill up icon to move the granularity to one level up i.e. from Beverage_prep to Beverage.
Step 5: Repeat the same step as highlighted above.
Step 6: Similarly, right-click on the Line and Stacked Column chart visual in the right-hand bottom corner and select Drill up.
It will drill up the visual to one level up i.e. Beverage to Beverage_category. You will notice that the Drill up icon is disabled as Beverage_category is the highest level of granularity.
Note that the Action bar is not enabled for the Donut chart i.e. you can’t apply Power BI drill up and drill down to Donut chart visuals.
Example #3
In this example, we will demo another example of using the Drill Up option in Power BI using a Superstore_US_Regional dataset. The superstore_US_Regional dataset contains sales details along with information about shipping modes.
For this example, we will leverage the same steps as mentioned in other examples to use Drill Up option.
Step 1: Create data visualization in Power BI by importing the dataset and selecting the visual icon from the Visualizations pane. Here we have defined the hierarchy structure to 5 levels i.e. Ship Mode to Region.
Step 2: Right-click on the visual in Power BI and choose the Drill up option.
This will move the visual to one level up i.e. Sub-category level.
Step 3: Repeat the same process to move to the Regional level granularity.
You can see that the Drill up icon is disabled as we are at the highest level of granularity.
Important Things to Note
- Drill Up works well when you have a hierarchy structure in your dataset. Define the hierarchy properly to create a meaningful Drill for your visuals
- You can enable/disable the Drill up options by navigating to Visualizations – Format your visual – General – Header icons – Icons
- Drill Up feature by default doesn’t apply any filtering to other visual elements in a report. If you are using Drill Up functionality, make sure that you understand its impact on cross-filtering or cross-highlighting
- You can use the Drill Up feature in combination with Power BI interactions to understand your data insights accurately and in-depth
Frequently Asked Questions (FAQs)
Yes, you can customize some aspects of Drill Up behavior in Power BI. These include:
• Choosing the Drill up levels for your data analysis as per the hierarchy levels.
• Ability to turn on the Drill Up icon for your reports by navigating to the General tab – Header icons – Icons and switch off the Drill up slider. Note, that this setting is only applicable in the reading view.
Some of the key limitations to Drill Up in Power BI include:
• Drill Up feature by default doesn’t apply any filtering to other visual elements in a report. However, if you are using advanced filtering options such as cross-filtering or cross-highlighting, your Drill up behavior may be impacted.
• Drill Up requires a Power BI Pro or Premium license to avail of such features. Ensure that the users who are using Power BI Desktop conform to licensing requirements.
• Drill Up feature works only for the visuals where you have a defined hierarchy structure.
• Any redesign or alteration to the hierarchy structure in a Drill Up is not possible.
The advantages of using Drill Up in Power BI include:
• Drill Up allows you to analyze the data points at each hierarchy structure and understand the associated patterns or trends.
• It helps you to navigate through different hierarchies in a structured manner without the need to create customized reports for each hierarchy level.
• Drill Up assists you in intuitive user reporting by enabling users to perform data analysis at each granular level.
As of now, Power BI doesn’t provide any keyboard shortcut for Drill Up functionality to the users.
You can use the Action Bar for using the Drill Up feature in your visual. Similarly, you can customize your keyboard settings to use Drill Up feature in Power BI.
Recommended Articles
This has been a guide to Power BI Drill Up. Here we learn how to use power bi drill up option with examples, and points to remember. You can learn more from the following articles –
Leave a Reply