Power BI Drill Down

What is Drill Down in Power BI?

Drill Down in Power BI is a feature that allows users to explore hierarchical data by expanding or drilling into more detailed levels of information. It is beneficial for visualizing and analyzing data with a hierarchy structure, such as dates, geographical locations, organizational structures, or product categories. For example, most common visuals contain date hierarchies such as year, quarter, month, and day. The easy way to identify if your visual has a hierarchy is to hover over it and see if you get drill control options in the action bar below.

In the subsequent section, we will see how to use a Power BI drill-down option.

How to Use Drill Down Option in Power BI?

Let’s apply the drill-down option by creating a report in Power BI. To use the drill-down option in Power BI, follow these steps:

Step 1: Open Power BI Desktop and create a new or open existing report.

In this case, we have created a report on the Sum of Units Sold by Segment across all the years using the Financials dataset.

Step 2: Add a visual (e.g., a column chart, bar chart, or matrix) that supports drilling down to the report in Power BI.

We have selected the Stacked Column Chart option from the Visualization pane.

Step 3: In the Fields pane on the right-hand side, select the column representing the hierarchy you want to drill down into (e.g., Date, Location, Category). Drag and drop the hierarchy column onto the appropriate area of the visual, such as the Axis, Legend, or Values section.

It will create a report or visualization in report canvas in Power BI.

Step 4: Now, hover over the visual or report. You will see drill control options in the action bar. Choose the single downward arrow to turn on the drill-down option in the action bar in Power BI.

Once you click on the drill down option, the drill mode will be on, and you can choose a data point to drill data.

Step 5: Choose the Expand Down icon from the action bar.

The bar chart will show the total units sold per segment, product, and Year, i.e., all the hierarchy levels.

Step 6: Right-click on the visual and select Show as a table option.

It will display the data used to build the visual each time you drill or expand it.

Step 7: Now, choose the Drill up arrow icon to move one level up, i.e., Sum of units sold by segment. You will notice that the data in the table range has changed from Step 6.

Step 8: Choose the double arrow drill-down icon to move to the next hierarchy level.

It will take the visual to the next level in the hierarchy, i.e., the Sum of Units sold by the product.

When you interact with the visual, such as by clicking on a specific bar in a column chart, it will drill down to the next level of detail based on the specified hierarchy column. You can continue drilling down further if additional levels are available.

Note: Drill-down functionality moves you to the next level of the hierarchy. By choosing to expand functionality, you move to the next level of the hierarchy, and it also preserves the data of the level you’re on.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Drill Down Feature For Non-Date Columns

The drill Down feature is not limited to date columns in Power BI. While date hierarchies are commonly used for drill down (e.g., Year > Quarter > Month > Day), you can apply to drill down to any hierarchical data structure in Power BI.

For example, we must drill down the report at segment, category, and sub-category levels for sales orders. Therefore, we have created a hierarchical table by dragging and dropping fields onto the Rows and Columns section of the table, as shown below:

Here we have created a hierarchy structure as Segment – Category – Sub-category.

Once you have selected the fields, choose the Matrix option from the Visualization pane.

It will create a hierarchical matrix Power BI Drill down table.

Click on the double arrow drill-down icon to move next level in the hierarchy. It will move one level up to the Category level as shown below.

Similarly, click on the Expand Down icon from the action bar. This will expand all the hierarchy levels in the report, as shown below:

You can create a Power BI Drill Down report to create a more detailed view of the data by using interactive visuals, and this can be achieved by using the Drill through a feature in the Visualization pane in Power BI.

Similarly, you can also Power BI Drill down to another page in Power BI using the Drill through option. For example, to drill down to another page, you can follow the steps highlighted below:

Step 1: Create a report with a hierarchy structure by dragging the fields from the Data pane into X-axis and Y-axis in the Visualization pane

Step 2: Select the field you want to apply drill through, and choose the Used as a category option from the drop-down menu. You can also see an arrow icon displayed to navigate to the previous page in the report.

Step 3: Select the Product Paeso from the filter and click on the arrow icon.

It will navigate to the previous page in the report, as shown below.

Important Things to Note

• Drill-down is only applicable to visuals that support hierarchical data, such as column charts, bar charts, matrices, and certain custom visuals.
• You need to have a well-defined hierarchy in your data model for drill-down to work correctly. Ensure that the data columns representing the hierarchy are properly related in the data model.
• The drill-down behavior can be customized by configuring the drill options in the “Drill” section of the “Visualizations” pane.
• By default, drilling functionality doesn’t filter other visuals though this setting can be altered through the report designer. When you perform drill modes, check if the other visuals on the page or report are cross-filtering or cross-highlighting
• Drill-down interactions can be combined with other features like filtering, highlighting, or cross-filtering to gain deeper insights into the data.
• It’s important to carefully design your visuals and consider the optimal levels of detail to include in the hierarchy to avoid overwhelming the user with excessive drill-down options.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI Drill Down. Here we learn how to use drill down option in power bi to expand the data in more detailed levels with examples. You can learn more from the following articles –

• Power BI Drillthrough
• Power BI KPI
• Power BI Security