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 at 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 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.
How to Use Drillthrough Filter in Power BI?
To apply 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.
You can also download the excel workbook from the below link which is used for this example.
- Now open Power BI software and upload the data under “Get Data”.
- Choose the excel file from the saved location.
- Your preview of the upload should look like this.
- Click on “Load” and it will come and sit under the “Data” section.
- Come back to the “Report” section and insert the “Donut” chart for each manager-wise summary from Visualization tools.
- Drag the “Manager” data to Legend filed and “Unit Sold” to the values field.
- Now our Donut chart looks like as shown below.
- Now create one more page as “Market-wise Sales”.
- In this page to create Market-wise Sales Summary drag the manager, market, and unit sold to values filed.
- Your Market-wise Sales summary table is ready.
- Create one more new page and name it as “Sales Graph”.
- In this sheet create a “Market-wise” funnel chart.
- The funnel chart for market-wise is shown below.
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 field list right click on “Manager” column and choose “Add to Drillthrough”.
- Now we can see this in the “Drill through Filter” pane.
- 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.
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”.
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.
- After setting the drillthrough the field now comes back to “Page 1” where we have created a “Manager-wise” sales summary of “Donut Chart”.
On this page, we are seeing the overall sales performance of each Manager from different markets. Manager “Ritu” has achieved the 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.
- As you can see above, we have an option of “Drillthrough”, place a cursor on that to see inside options.
- 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.
Wow!!! It has automatically jumped to the page “Market-wise Sales” and in 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 Ctrl key.
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.
- Now it will take us to the “Sales Graph” page with only “Ritu’s” data filtered.
This is the overview of the “Drillthrough” filter option in Power BI.
Note: I have done so much of formatting to this table and charts, you can download the Power BI Drillthrough file from below link and apply each formatting technique as applied.
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 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 –