Drill Down in PowerBI

19 April 2023
Eugene Lebedev

Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.

Introduction

In the world of business intelligence, visualising data in an understandable and comprehensive way is essential. 

Drill down for Power BI is a great tool for presenting hierarchical data that allows users to explore data at different levels of granularity. 

In this blog, we will discuss the practical implementation of drill down in Power BI using an example dataset. We will explore how to activate drill down mode and how to use drill down options to view sales data across multiple dimensions, including Group, Country, and Region. By the end of this process, you will have a better understanding of how to use a Power BI drill down to make your reports more informative and efficient.

Drill Down

Drill down is a great way of presenting hierarchical data. It allows the report users to see the highest level of detail in the chart but also drill through Power BI data at will.

This feature is especially useful when your report has multiple or hierarchical dimensions and you want to visualise them all together in a single chart. Instead of cheating several charts, we can save some space on the canvas by creating one chart with a Power BI drill down. 

See also  How to Design a Professional-Looking Power BI Dashboard

To see how you can optimise your data reporting with Power BI, make an enquiry here. 

Add a Drill Down

Let’s take a look at how to practically use a Power BI drill down using the dataset below. We have a report with a Bar chart showing sales by Group. In addition, we have 2 more dimensions that are more granular: Country and Region.

Power BI Drill Down Data

Drill down chart 2 fields

Currently the chart has 2 fields (Group in X axis and Sales in Y Axis). In order to add a drill down, we will add remaining dimensions to the X axis in the same hierarchical order as it will turn up in the chart for the users:

drill down remaining dimensions added to x axis

Once these fields are added now drill down Power BI options will be enabled on the chart.

How to use drill down and Power Bi drill through

There are two ways by which users can access drill down functionalities in Power BI:

  • Right Click on the Bar – Users can right click on any bar and Power BI will show a drill down option. Upon the click on this option, Power BI will apply the drill down.

In our report we can click the drill down option on the bar that shows North America. As a result, Power BI showed us the sales from the countries that are grouped under North America. Before the drill down, you can see that the sales of North America are 11M. After drilling down we can see how 11M is split between 2 different countries (9.1M for the United States and 1.9M for Canada).

how to use drill down step 1

how to use drill down step 2

Since we have another dimension available (Region) so let’s do another drill down on the United States bar. Right-click on the United States and then left-click on Drill down.

See also  From Data to Insights: The Role of a Power BI Consultant in UK SMB

As you can see, the 9.1M in sales for the United States is distributed among different regions as follows.

how to use drill down step 3

As you can see, by using Power BI drill down we can see sales across this hierarchy :

Group → Country → Region

If you want to go back from Region to Country, you can do this by using the upward arrow as marked below:

how to use drill down step 4

  • Activating Drill Down mode (downward arrow): There is another way to drill down in Power BI. Click on the down arrow as marked in the screenshot below to enable Drill down mode. Now you can left-click on any bar chart to drill down to the next hierarchy

how to use drill activating drill down mode

  • Go to the next level of hierarchy (2 downward arrows): This option will show the next level of hierarchy with all the available options. Click on below highlighted icon to access it and will show you the results as follows:
    how to use drill next level of hierarchy
  • Expand all down (One arrow splitting into 2): This option will expand all the available Countries into regions that make them up and show them all together. Click on the below marked icon to implement this feature:
    how to use drill expand all down

Conclusion

In conclusion, drill down is a powerful tool in Power BI that allows report users to explore data at different levels of granularity. By implementing drill down and drill through in Power BI, you can save space on your canvas and present hierarchical data in a single chart. In addition, by using drill down effectively, you can make your reports more informative and efficient, and ultimately help your organisation make data-driven decisions.

See also  How to Create SEO Dashboard Reports Using SEMrush data

Microsoft Power Platform

Everything you Need to Know

Of the endless possible ways to try and maximise the value of your data, only one is the very best. We’ll show you exactly what it looks like.

To discuss your project and the many ways we can help bring your data to life please contact:

Call

+44 7846 623693

eugene.lebedev@vidi-corp.com

Or complete the form below