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.
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.
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.”
- On 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 chartFunnel ChartFunnel charts in Excel are used to represent data behavior at each stage and as the values decrease, the chart takes on the shape of a funnel. This funnel chart feature is only available in Microsoft Office 2019 or later versions..
- 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 the field list, right-click on the “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 drill through, the field now comes back to “Page 1,” where we have created a “Manager-wise” sales summary of “Donut ChartDonut ChartA doughnut chart is a type of excel chart whose visualization is similar to pie chart. The categories in this chart are parts that, when combined, represent the whole data in the chart. A doughnut chart can only be made using data in rows or columns..”
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.
- 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 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.
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 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.
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 –