Drill throughs are an amazing way to go deeper into your analysis in Power BI.

Imagine this: you are presenting your customer analysis report findings to your manager on the total customer acquisition by territory this year and he/she asks you to show the details by city on where the acquisition took place. 

I bet if your report was in Excel you would have to say that you will get back to your manager after the meeting and then painstakingly crunch the data again.

However, thanks to the drill-through feature in Power BI, we don’t have to do that any more. The detailed analysis is right at your fingertips with the click of a button. 

In this blog, let’s walk through how we can set up the drill-through feature in Power BI and ways we can use this feature to better improve your analysis.

What Is A Drill Through?

A drill-through in Power BI is a feature that allows users to navigate from a summary-level report to a detailed report by clicking on a specific data point.

This functionality enables users to "drill through" the summarized data to see more granular details related to that particular data point, providing deeper insights and context within the data analysis process. For example, clicking on a sales figure for a particular product in a summary report could take the user to another report showing detailed sales transactions.

How To Set Up Drill Through in Power BI

Setting up a drill-through can be quite simple like with most things in Power BI! Let us go through a step-by-step guide on how to set it up so you can use this knowledge in your next report.

Step 1: Create a high-level summary page

This page will be where the user will first land when they start using the report and can contain summarized numbers such as total customers by territory as shown below.

Step 2: Set up a details page for drill-through

The next step is to create a detailed breakdown of the summarized data that you want to see broken down. For example, let's say we now want to see which country and city the customers are coming from. 

To do this, create a new tab in Power BI Desktop and name it “Breakdown by Country/City”. In this tab, simply create a new tabular chart which has the country and city-wise breakdown of total customers as such:

This is a simple demonstration of a matrix table but you can customize this table according to your requirements of course!

Step 3: Setting up the drill through logic

Now it’s time to set this page as a potential drill-through destination. To do this we have to reference the dimension which is being used in the summary tab visual. For instance, here we are trying to see the breakdown of territories in the drill-through tab. 

Hence, we simply drag and drop the territory field into the drill-through option in the newly created “Breakdown by Country/City” tab.

Step 4: Trying out the drill through functionality

Once we have set up the drill-through feature, hide the tab that we just created for drill-through and return to the summary tab from which we want to drill through from. 

Right-click on the part of the visual you want to see details of hover on drill through and click on the name of the drill through tab that we just created. 

This will directly take us to the drill-through tab and interestingly the table will only show country and city data for Norway. 

You will notice that the table is already filtered for the EMEA-Norway territory since you performed the drill-through action for this territory only. 

Please note that drill-through is not limited to only one tab. You can perform this action for as many pages as you want to depending on how many ways you want to break down the data. 

In the example below, you will notice that we are now drilling through both by country/city and segments. This flexibility allows you to see the data at any number of levels helping you answer any question your manager might have. 

Some Tips for Best Practices

To ensure we are using the drill-through features in a clear and concise manner, we must remember a few things. 

• Clear Navigation: Provide clear navigation or buttons on the summary report to help users easily navigate to the drill-through page. You can create a blank button and provide the drill-through action so it's visually intuitive for the user. The user can just select a territory and click on the button to go to the drill through the tab. 

• Relevant Data: Only include relevant data in the drill-through report to avoid overwhelming users with unnecessary information.

• Performance Considerations: Be mindful of performance, especially if dealing with large datasets or complex visuals.

In Closing

Drill through functionality in Power BI is a powerful tool for detailed data analysis. By following the steps outlined in this blog post, you can create intuitive and insightful drill throughs that enhance your data storytelling. With careful setup and thoughtful design, drill through reports can provide your managers and users with deeper insights and a more interactive data exploration experience.

We hope this blog helps you effectively implement drill through in your Power BI reports. Happy reporting and keep learning!

To learn more such tips on Power BI, don’t forget to read Case When’s other articles on Power BI. 

FAQs

What is the difference between drill down and drill through?

Drill down navigates through hierarchical data levels within the same visual, like moving from yearly to monthly sales. Drill through, as shown in the article, moves to a different report page for detailed information on a specific data point, such as detailed sales for a product. Drill down stays within one visual, while drill through navigates to another page for more insights.

Can I drill through a table or matrix visualization?

Yes of course you can! Drill through is supported for tabular charts as well as other commonly used charts such as bar charts, line charts etc.