Understanding Microsoft Power BI’s default drilling
Your email has been sent
Adding drill through functions to Power BI data visuals lets users group data visuals on the main page alongside the details. Here’s how you can add drill through visuals in your Power BI reports.
Being able to drill down through a summarized visual to details or specific facts is an expected feature in dashboarding. Power BI supports this functionality, but it isn’t automatic, so if you publish a summary visual without setting up drill through visuals, you might end up with frustrated end users. The good news is that while this functionality isn’t automatic, it is easy to set up.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
In this tutorial, I’ll explain how to add drilling functionality to a summary visual in Microsoft Power BI. We’ll also discuss training end users on how to use the drill through feature because it doesn’t work with a double-click, as some might expect. Throughout this tutorial, I’ll 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:
Drilling through related data is an established feature end users will likely expect. Drilling is a great way to examine details; the function can be used to explore specific facts, gaining insight the original visual doesn’t provide. For example, if an end user is viewing a monthly sales report, they might use drilling to see all orders for that month.
SEE: The Complete Microsoft Power BI Super Bundle (TechRepublic Academy)
Think of drilling as a way of looking for clues about specific data that is displayed in a visual or report. It’s a bit like holding a lens to the main visual to read the fine print. End users will most often use this feature when they want to see specific data rather than the aggregate.
Now, let’s examine a simple dataset to see how end users can benefit from drilling in Power BI.
Let’s suppose you’re basing visuals on the sample dataset shown in Figure A.
Figure A
Specifically, you want to display a monthly sales report (Figure B). In this scenario, end users might want to see the actual sales for a specific month, but when they double-click on a month entry, they get no additional information. They may 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 display the facts or data evaluated in those summaries. We’ll use a donut visual to display a monthly summary of sales based on the dataset shown earlier in Figure A. In the next section, we’ll build the drill through visuals.
To get started, you should base a donut visual on the Amount and Date fields in the TableSales table. Using the Visualizations pane, drag Date to the Legend bucket. Power BI will add a Month field, which it will use to summarize the data. You can specify a different date component by expanding Date Hierarchy.
Next, drag the Amount field to the Values bucket. Power BI renames it to Sum of Amount because Amount is a math column; Power BI assumes you will run calculations on this field. You can rename the field in the visual if you would like to.
If you’re wondering where the Date Hierarchy came 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, saving you a lot of work. Power BI uses the Auto date table, which you can’t see, but it’s used to create Date Hierarchy.
To learn more about hierarchies in Power BI, you can read How to build a hierarchy to support drill mode in Microsoft Power BI. To learn more about date tables, you can read How to know if the Auto date table is adequate when using Power BI and How to create a date table in Microsoft Power BI.
Now, let’s add a drill through visual Power BI can display when an end user wants to see all of the sales records for the month we’ve selected in the donut visual.
End users might want to see the details evaluated each month’s summary values shown in the donut visual. Right now, there’s no drilling in the visual. We’ll need to add drill through visuals — the visuals that will display details about monthly sales.
To get started, add a new page; drill through works with pages only. You can change the page name if you like, but it isn’t necessary. Then, add the visual you want to display the detailed records for each month.
Remember, the summary visual — the donut visual — displays monthly totals. We want a visual that displays all of the sales records for the currently selected month in the donut visual. For instance, 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 Region because there’s no drilling on Region, 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 Drill Through from the resulting dropdown (Figure C).
Figure C
If you check the Visualizations pane, you’ll see that Power BI added the Amount field to the Drill Through bucket, as shown in Figure D.
Figure D
Everything is in place now, and the drill through functionality is ready to use. To use it, return to the donut visual on page one and right-click May. Then, choose Drill Through and Sales Details, which is on page two if you didn’t rename the page when you added it (Figure E). In response, Power BI shows the table visual filtered to display only the records for May, the selected month in the donut visual.
Figure E
Figure F shows the drill through table, filtered to display only two records because there are only two sales records for May. Power BI filters the records by the month selected in the donut 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 donut and the table visual, this arrow will always take you back to the donut 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:
If you’re working with end users who aren’t familiar with Power BI, you should take the time to show them how to use the drill through functionality. Otherwise, they might miss the feature altogether.
SEE: Quick glossary: Business intelligence and analytics (TechRepublic Premium)
You might be wondering why you’d force end users to right-click and choose a drill through visual when you can use a filter on the same page. Most reports have a lot of visuals, but drill through allows you to group several high-end visuals on the main page while still allowing end consumers 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.
Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays
Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays
Understanding Microsoft Power BI’s default drilling
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
Zero-trust security operates on the fundamental premise that trust should never be assumed, regardless of whether a user or device is inside or outside the corporate network. In this TechRepublic Premium guide, we will explore the benefits of incorporating zero-trust security into your organization’s cybersecurity framework to prevent data breaches and strengthen the protection of …
Every organization relies on data that must be protected and backed up in a reliable and secure way by authorized personnel. While the content or criticality of the data may vary, the processes behind a successful backup strategy are universal. This checklist from TechRepublic Premium will help you establish a standard framework for backing up …
Artificial intelligence comes in many varieties, from tools that respond to customers via chat to complicated machine learning algorithms that predict the trajectory of an entire organization. Despite years of activity and the imaginative ideas found in science fiction, AI doesn’t yet comprise sentient machines that reason like humans. Rather, AI encompasses more narrowly focused …
Data governance is no longer a ‘nice-to-have’ measure in organizations as there is a growing concern around the globe that unregulated use of data can result in serious privacy violations, among other issues. That’s why data governance has topped the agenda in many continents as more efforts are being put in place to provide standards …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply