Power BI Drillthrough

Drillthrough in power bi is like an indicator which is used to focus on a specific type of data or a specific type of value generated or displayed in a report, it is very helpful when a business user has to focus on only certain elements rather than the whole portion of data.

Drill Through in Power BI

In Power BI, Drillthrough is an option to navigate to different layers of the data of a selected category data set. For example, under one manger, there could be different employees, and in the overall picture, we have seen manger-wise sales performance, which comprises all his team members’ data. If you want to see “Manager 1” breakup of different employees, then Drill Down can help us to Drillthrough this information and analyze deeply.

Everybody wants the summary out of the big data; very few people want to see the breakup of the data. Those who want to see the breakup of the summary report are the ones who want to study the data in detail and see what is happening with each line item of that particular category of data.

Especially in Power BI, the user doesn’t have the luxury of seeing the breakup of the summary visual, and this is the common thinking everybody has those who are new to Power BI dashboards. However, in Power BI, we have the option of seeing the detailed data or analysis by using the “Drillthrough” option.

Power-BI-Drillthrough-1.png

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Drillthrough (wallstreetmojo.com)

How to Use Drillthrough Filter in Power BI?

To apply the Drillthrough filter in Power BI first, you need some sort of data, and below is the data you can use it to practice drill through.

Power Bi drill through (Data)

You can also download the excel workbook from the below link, which is used for this example.

You can download this Power BI Drillthrough Excel Template here – Power BI Drillthrough Excel Template
  • Now open Power BI software and upload the data under “Get Data.”
Power Bi drill through (Get Data)
  • Choose the excel file from the saved location.
Power Bi drill through (Location)
  • Your preview of the upload should look like this.
Power Bi drill through (Load)
  • Click on “Load,” and it will come and sit under the “Data” section.
Power Bi drill through (Data sheet)
  • Come back to the “Report” section and insert the “Donut” chart for each manager-wise summary from Visualization tools.
Power Bi drill through (Insert donut chart)
  • Drag the “Manager” data to Legend filed and “Unit Sold” to the values field.
Power Bi drill through (drag fields)
  • Now our Donut chart looks like as shown below.
Power Bi drill through (donut chart)
  • Now create one more page as “Market-wise Sales.”
Power Bi drill through (market wise sale)
  • On this page, to create Market-wise Sales Summary, drag the manager, market, and unit sold to values filed.
Power Bi drill through (Value fields)
  • Your Market-wise Sales summary table is ready.
Power Bi drill through (market wise table)
  • Create one more new page and name it as “Sales Graph.”
Power Bi drill through (Page 3)
Power Bi drill through (Funnel chart option)
  • The funnel chart for market-wise is shown below.
Power Bi drill through (Funnel chart)

Now we need to add the “Drillthrough” field on each page. Since we need to have a breakup of each Manager-wise from the first page, we will add “Manager” to the drill through a field.

  • Now come back to the second page i.e., “Market-wise Sales” in this sheet from the field list, right-click on the “Manager” column and choose “Add to Drillthrough.”
Power Bi drill through (Manager)
  • Now we can see this in the “Drill through Filter” pane.
Power Bi drill through (Drill through)
  • One more thing you need to look at is as soon as you add a field to drill through, we can see the automatic back button appears on the top left-hand side of the page.
Power Bi drill through (back)

We will come back to this in a moment.

  • Now come to the “Sales Graph” page and do the same thing of adding a “Manage” column from the field to “Drill Through.”
sales graph drill through

So on this page also you could see the same column in the “Drillthrough filter” pane.

Now we are all set to execute the “Drill through” option. At this point in time, you have no idea how this Drillthrough will thrill through your mind and heart. Let’s learn about it now.

Manager wise summary

On this page, we are seeing the overall sales performance of each Manager from different markets. Manager “Ritu” has achieved a sales amount of 13.99 K, which comes from different markets now. As a reader of the report, you would like to know in which market she has got more revenue, which is not available with this visual.

So, how do you see that information???

  • This is where all our above efforts pay off, now right-click on the “Ritu” portion of the donut chart.
Ritu records
  • As you can see above, we have an option of “Drillthrough.” Place a cursor on that to see inside options.
Drill through Ritu
  • Ok, in this, we could see our page names where we have added the “Manager” column as the “DrillThrough” option. Now click on “Market-wise Sales” to drill through to that page to see the breakup of “Ritu” revenue from different markets.
Ritu Revenue

Wow!!! It has automatically jumped to the page “Market-wise Sales,” and on this page, we could see only “Ritu’s” breakup data of revenue from different markets.

How cool is this???

  • Now, if you want to go back to the main page from where we have come from, click on the “Back” button at the top left of the page by holding the Ctrl key.
back to previous (Ctrl + Click)

This will take back to the original page from where you drilled through.

  • Similarly, if you want to see “Sale Graph” of “Ritu,” then right-click and choose “Sales Graph” from Drillthrough options.
Sales graph of Ritu
  • Now it will take us to the “Sales Graph” page with only “Ritu’s” data filtered.
Power Bi drill through (sales graph)

This is the overview of the “Drillthrough” filter option in Power BI.

Note: I have done so much formatting to this table and charts. You can download the Power BI Drillthrough file from the below link and apply each formatting technique as applied.

You can download this Power BI Drillthrough Template here – Power BI Drillthrough Template

Things to Remember Here

  • Drillthrough options in power bi work based on the page settings you do on each respective page.
  • In Power BI, Drill through can provide the breakup for the consolidated report.
  • By using the “Back” button, we can navigate back to the original page.

This is a tutorial on Power BI Drillthrough. Here we discuss how to apply the Drillthrough filter in Power BI to navigate to different layers of data along with examples. You may learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>