Being able to drill through a summarized visual to details or specific facts is an expected feature in dashboarding. Power BI supports this functionality, but it’s not automatic, so if you publish a summary visual without setting up drill-through visuals, you could end up with frustrated end users. The good news is that while this functionality isn’t automatic, it’s easy to set up.
TO SEE: Hiring Pack: Microsoft Power BI Developer (Tech Republic Premium)
In this tutorial, I explain how to add drill functionality to an overview visual in Microsoft Power BI. We also discuss training end users to use the Drill Through feature, as it doesn’t work with double-click as some might expect. Throughout this tutorial I will be using Power BI Desktop on a Windows 10 64-bit system.
Here you can download the Microsoft Power BI demo file for this tutorial and follow along.
Jump to:
Why you should consider drilling into Power BI
Drilling through related data is an established feature that end users are likely to expect. Drilling is a great way to examine details; the feature can be used to investigate specific facts, gaining insight that the original visual doesn’t provide. For example, if an end user is viewing a monthly sales report, they can use drills to see all orders for that month.
TO SEE: The complete Microsoft Power BI Super Bundle (TechRepublic Academy)
Think of drilling as a way to search for clues about specific data displayed in a visual or report. It’s a bit like holding a lens over the main image to read the fine print. End users will use this feature most often when they want to see specific data rather than the total.
Now let’s look at a simple dataset to see how end users can benefit from drilling in Power BI.
Prepare the summary visual for drilling
Let’s say you base visuals on the sample dataset shown in Image A.
Image A
Specifically, you want to display a monthly sales report (Figure B). In this scenario, end users may want to see the actual sales for a specific month, but when they double-click a month entry, they don’t get any additional information. They can then look around, but find nothing that shows them the sales information they want for each month.
Figure B
To set up drilling, you need a summary visual and any number of visuals that represent the facts or data evaluated in those summaries. We use a donut visual to display a monthly breakdown of sales based on the data set shown earlier in Figure A. In the next section, we’ll build the drill-through visuals.
To get started, you must base a donut visual on the Amount and Date fields in the TableSales table. Using the Visualizations pane, drag Date into the legend bucket. Power BI adds a Month field, which is used to summarize the data. You can specify a different date component by expanding Date Hierarchy.
Then drag the Amount field to the Values bucket. Power BI renames it to Sum of Amount because Amount is a mathematical column; Power BI assumes that you perform calculations on this field. You can rename the field in the visual if you want.
Where does the data hierarchy come from in Power BI?
If you’re wondering where the date hierarchy comes from, Power BI creates it when you import time and date values by automatically adding each date’s month, day of the week, year, and so on. This is one of the things Power BI does internally that saves you a lot of work. Power BI uses the automatic date table, which you can’t see, but it’s used to create a date hierarchy.
Read to learn more about hierarchies in Power BI Build a hierarchy to support drill mode in Microsoft Power BI. For more information on date tables, read How to know if the automatic date table is suitable when using Power BI and Create a date table in Microsoft Power BI.
Now let’s add a visualization that Power BI can display when an end user wants to see all sales records for the month we selected in the donut visual.
Add a drill-through visual in Power BI
End users may want to see the details evaluated in each month’s summary values displayed in the annular visual. There is currently no drilling in the image. We need to add drill-through visuals — the visuals that show monthly sales details.
To get started, add a new page; drill through only works with pages. You can change the page name if you want, but it’s not necessary. Then add the visual whose detailed records you want to see for each month.
Remember that the summary visual – the donut visual – displays monthly totals. We want a visual that displays all sales records for the currently selected month in the donut visual. For example, if you select May, you want to see the sales records for May.
On page two, add a table visual based on all three fields. We don’t need Regio because Regio can’t be drilled, but end users might want to see it.
Now let’s add Amount as the drill through field. Right-click the Amount field in the Fields pane and choose Add To Pierce from the resulting drop-down list (Figure C).
Figure C
If you check the Visualizations pane, you will see that Power BI has added the Amount field to the Drill Through bucket, as shown in Figure D.
Figure D
Use drill-through in Power BI
Everything is now in place and the pierce functionality is ready to use. To use it, go back to the donut visual on page one and right-click on May. Then choose Drill Through and Sales Details, which is on page two if you didn’t rename the page when you added it (Figures E). In response, Power BI shows the table visual filtered to show only the records for May, the selected month in the ring visual.
Figures E
Figure F shows the drill through table, filtered to show only two records because there are only two sales records for May. Power BI filters the records by the selected month in the ring visual.
Figure F
To return to the donut visual, hold down the Ctrl key and click the back arrow in the top right corner. Regardless of the number of pages between the ring and the table visual, this arrow will always take you back to the ring visual. It’s part of the drill-through feature that Power BI added when you added the drill-through field.
There are a few additional things you’ll want to know about this process:
- Clicking the page two tab does not update the visual. You must use the right-click route.
- As it is, you can drill per month.
- Drill-through fields must be in the original summarized visual, in our case the annular visual. This limitation makes sense, but it requires a bit of planning so that the drill-through visuals can show the details your end users want to see.
- You can always add more drill-through visuals. Remember that each needs its own page.
If you’re working with end users who aren’t familiar with Power BI, take the time to show them how to use the drill-through functionality. Otherwise, they may miss the feature altogether.
TO SEE: Short Glossary: Business intelligence and analytics (Tech Republic Premium)
You may wonder why force end users to right-click and choose a drill-through visualization when you can use a filter on the same page. Most reports contain many visuals, but drilling allows you to group several high-end visuals on the main page while still allowing end users to see details. It’s another way to arrange data that can make data visualization easier, depending on the dataset and use cases you’re working with.
