If you’ve spent any time working with Power BI, you already know that it provides a great platform for visualizing and interacting with your data. You can build wonderful visualizations just by dragging and dropping fields into the UI, and clicking on a visual can further filter other visuals on the page.
Another tool for investigating your data is drilling through a visual to get more information regarding a summarized field or field entity.
In this blog, we’ll cover how to create a drill-through in Power BI and when you may want to use one.
What is a Drill Through and Why Does it Matter?
A drill-through is a form of navigation between report pages. Drill through consist of a report page and the destination page (or drill through page), which is often hidden. The functionality allows the user to “drill through” a specific data point on a visualization to the destination page, where they are met with one or more additional visualizations for added context.
When a user drills through a data point, all of the filter and slicer selections will be maintained on the destination page. In other words, drill-throughs allow the report user to focus on a specific entity or summarized value within the report and provide a more in-depth view.
When to Use a Drill Through
A drill-through can be used anytime you need to create the same report with details for multiple entities. For instance, you have sales divided by regions, and you need to see the same set of information for each of them.
You could create a new page for each region, but a drill-through allows you to use one set of visuals for all of the regions, and the visuals will reflect the appropriate values depending on which region was drilled through. You might show the regional manager sales vs. profit margin or how many days it takes to ship products after receiving the order.
You can also use a drill-through when you want to take a deeper look at a summarized value. If, for example, you are visualizing the sum of sales, you could drill through to get a better understanding of what is creating that summarized value. Is it a few customers doing most of the heavy lifting or a small selection of products, or maybe it’s one salesperson outselling their coworkers on a regular basis?
Before creating the drill-through, it is important to understand your goal. That is, what do you want to know more about? This will determine your drill through the field or fields.
How to Create a Drill Through
Once you have your use case in mind, it is time to actually create your drill-through. You’ll start by creating a new page. This will be the drill-through page in the Power BI report. On this drill-through page, create the visuals that will provide more information for the data on the main page.
For example, you could create a simple table visualization with more granular row-by-row details that the user drills through from a more summarized bar chart on the main report page.
Once you have the page set up with the visuals you want, it is time to add the drill-through field. In our example, we will use the public Superstore dataset and drill through the Region field. At the bottom of the Visualizations pane is an area for drill-through.
You will simply drag the field you wish to drill through into the drop box. You then have to set whether to drill through when the field is used as a category or when summarized using the provided drop-down.
In this example, we will set it to “Used as category”.
While there, take note of the two toggles. The first is “Cross-report,” which we will cover briefly in the next paragraph. The second is the “Keep all filters” toggle. This toggle is extremely important for how your drill-through reacts when used.
If it is toggled off, the user will have to set any filters or slicers beyond the drill-through entity they chose when drilling through. This may not be very helpful if they had already set filters and slicers on the page drilled through. On the other hand, if you have this toggle on, then all of the filter and slicer selections from the page that was drilled through will apply to the visuals on the drill-through page as well. These might be date ranges, product categories or subcategories, or market segments.
The Cross-report toggle is slightly more advanced and allows a user to drill through to this report page from a different report within the same workspace or app with related content. It also allows the user to bring across their filter from the other report.
You, as the designer, need to keep this in mind when designing the drill-through page. If the user has a field that can be filtered on the other report, it is best to have that field available on this drill-through report. Not having it available could have unintended consequences.
Once you have created your drill-through page and set your drill-through field and toggles to your liking, it is ready for use. The user can now right-click wherever the drill-through field is used on a different report page and choose to drill through to this page to see additional information in a prearranged fashion.
In the below example, the West region was right-clicked on, which will allow drill through to the Region Overview page.
Below is the resulting region overview page. Notice how the visuals have changed. They now only reflect data specific to the West region, and the dates reflect those chosen on the Exec Summary.
Extras
There is no limit to how many drill-through pages you include in your report. If you have an executive summary page, you may want a drill-through for each visual on that page, including any KPI cards. KPI cards can be drilled through by setting the drill-through option to “Summarized” when creating your drill-through page.
Though not required, you may want to hide drill-through pages so that users don’t access them directly. A drill-through page that is accessed directly will show data from the previous drill, which is only good if that is what the user wants to see.
If you have hierarchical data (Country, Region, State) that you want to drill through, it is possible to add multiple drill-through fields. This ensures you can drill through at any level of the hierarchy. Keep the hierarchy in mind when creating the visuals on the drill-through page. Does it do a lot of good to show sales by subcategory if the user just drilled through a sales visual that was on the subcategories level of the hierarchy?
A drill-through page can be a single table. It is not uncommon for users to want to export the data after drilling though. This may be a case where you leave the “Keep all filters” toggle off and add some slicers for them to use as they wish.
If you are using dimension tables to build your visuals, make sure to use the field from the dimension table when creating your drill through.
Closing
A drill-through is a useful way to see the same data for multiple entities without creating a separate report page for each or providing a lot more detail for a summarized view. Its power lies in the fact that you create a single report page with visuals that provide further information on an entity, and this single page can be used for all entities within that field.
It can also be used to drill through for additional information regarding a summarized value.
If you need additional help or are curious about how to better leverage Power BI, reach out to the experts at phData today for help, guidance, and best practices!
FAQs
Drill down shows the next level in a hierarchy, while drill through takes the user to a separate page that provides further information related to the drilled-through entity or summarized value.
Yes, you can drill through a table visualization. The key to this is deciding whether you are drilling through the value, the row, or the column. If drilling through the value, you will want to set up the drill through the page to use the value field and summarize. If using the column or rows, use the appropriate field and Used as a category. Keep in mind if the user drills through on a value, the column and row act as filters, and they may need training to drill through on the appropriate column or row headers.
The most common inefficiencies that we experience typically boil down to design standards. A well-designed architecture means raw data gets enriched and standardized through multiple stages. As standards are defined, this data should be persisted to a physical table instead of joining views together or creating join columns via subselected queries. This can cause unnecessary table scans and longer run times.
The most common problem our customers have is ingesting data into Snowflake efficiently. This can manifest itself in multiple forms, file sizes, file counts, file speeds, and file types, and they all lead to increased credit consumption. This problem can typically be solved via tweaks to the ingestion process but sometimes does require tooling that compliments the Snowflake data cloud and helps it meet broader enterprise needs.
