close

How To Use Drill Down & Drill Up In Power BI

Posted by Errin O'Connor on Apr, 14, 2021 06:04

Drill down is nothing but the next level of hierarchical insights of the data. For instance, you present a year-wise sales summary. You may like to dig deeper into a Monthly summary, Quarterly summary, and even a day-wise summary. Hence, that’s where Power BI drill down functionality for data plays an important role in highlighting detailed and comprehensive drill down insights. 

You may be thinking about what makes this important. You see, the reason why drill-down is a very crucial Power BI feature is that you may view overall sales as 2 M in a yearly revenue chart. However, there are possibilities where most of the revenue is produced in a few months or a single quarter. Hence, drilling down the general view of the summer deeper will offer you the right picture. 

What Is Drill Down and Drill Up Functionality In Business Intelligence?

In case you didn’t know, drill down and drill up make reporting practical and powerful simultaneously. Drill down and drill up are two influential features in business intelligence. They both offer you the freedom to see information and data in data analytics tools. That’s especially true, even though they do so differently. 

Drill down is the capability that takes the user from a more general view of data to a more specific one at a simple click of a mouse. For instance, a report showing sales revenue by state enables the user to pick a state, click on it, and see sales revenue by city or county in that specific state.

That’s what we call “drill down,” as it’s a feature allowing you to go much deeper into more particular data or information layers being assessed. 

Drill Down Functionality in Power BI

On the other hand, drill up allows the user to zoom out the data or information stepwise. 

Remember that drilling down is a method that allows you to navigate through worksheet data easily, searching the answers to the questions experiencing by your business. The drilling to detail method allows you to search at the values that make up a specific summary value. Further, you can drill to associated items, adding connected data that is not presently added in the worksheet. 

Advantages of drill down and drill up function in data exploration

You will find many benefits of drill down and drill up function when it comes to data discovery.

Some of these benefits are the following:

  • It keeps reporting load light and improve overall reporting performance

Presenting just one layer of data allows the drill down function to lighten the overall load on the server at the query period. Therefore, it improves reporting performance while delivering excellent value to end-user at the same time. 

  • See data from different perspectives

Drill up enables users to evaluate the same data through numerous reports, evaluate it with several features and even highlight it through various visualization processes. That massively improves the understanding of different users about the data and of the reasons behind the numbers. 

  • Obtain immediate knowledge of various data depths

Drill down offers the users a much deeper insight into the data by allowing them to see what makes up the figures they are assessing. For instance, drill down answers questions in mere seconds like Which states are doing better on my National sales figure? Which states are currently underperforming? Which territories are driving revenue within every state? 

Power BI Consulting

Drill Down Vs. Drill Up Vs. Drill Through the process in Power BI

Are you one of those new to Business Intelligence (BI)? Then new concepts come along with new vocabulary, which could be the major learning obstacles you’ll encounter. That’s until you face a database that is more akin to a garbage can than a well-arranged library. 

Drill up, drill down, drill through—what does drilling have to do with reporting and dashboards? Was the individual coined these terms connected to a woodpecker? 

Here’s a quick overview of the major differences between these three: 

  • Drill Down 

The ideal way to learn how the drill down function works is to tell yourself that you are walking through various hierarchy levels. By definition, it needs the usage of hierarchical definition where values are categorized into levels. 

Here are great examples of levels: 

  • Do you have date-based data? Then the level of groupings might be like this: Year, Month, Day of Year, Quarter, Hour 
  • Do you have geographical data? Then the level might be like this: Country, Province/State, Postal Code, City 
  • Drill Up 

To make it simple, drilling up is the opposite of drilling down. It can be utilized as a data exploration entry point for those quickly changing information. 

Do you have daily data? It makes sense to default the dataset to present the bottom level to see the newest results. Drill up becomes a system allowing the user to explore more trends while seeing new results easily. 

  • Drill Through 

Meanwhile, drilling through is a just a way of saying navigation. However, it’s a crucial principle as it enables users to explore more related information by clicking on a visualization. In short, you’re taking what the user clicks and send them to another dashboard or report to present related data. 

Drill down and drill up reports in Microsoft Power BI

A drill down BI report allows users to pilot a different layer of data by steering and clicking a certain data element in an application or data element. Drill down lets users discover multidimensional data through steering from one level down to a more detailed level. 

That also enables users to see aggregated, summary information and then hierarchically discover deeper data levels for a more particular analysis. Also, drill down is one of the major functions of OLAP, allowing users to answer complicated multidimensional data concerns. 

How to setup drill through functions in Power BI (Setting up the hierarchy, drill pathways, and accessing drill features)

In Power BI, Drill is like an indicator that is utilized to focus on a particular type of data or a specific type of value displayed or generated in a report. It’s extremely helpful when a business user needs to concentrate on only specific elements instead of a whole portion of data. 

In Power BI, drill through is an option to steer different layers of the data of a chosen category data set.

Open your Power BI Desktop file or go to the report you have published already to the Power BI service. You can then go to the page you like to use as the target of your drill-through action. Begin editing the report page, and you will see that there’s a Drill through Filters section on the right corner. 

Left click on the visual of Power BI for Drill Down Options

A small back arrow will be added to the report after adding the field to the Drill through Filters section. That will enable you to easily return to the report page you used to invoke the drill through action.  

That’s it. Now you can easily invoke the drill through action on any other page within the report. If you like to invoke the drill through action, you can right-click a chart that utilizes the field you added to the Drill through Filters section. Choose drill through and pick your report. 

For instance, you picked the Life Skills bar in the bar chart and picked your Drill through action. That systematically will bring you the Subject Dashboard, the target of your drill through action, along with the proper filter applied. 

Drill down all fields at once and Drill down one field at a time

You will find two ways to drill down in your visualizations. Both of these methods accomplish the same outcomes. You can use the method you would want most. 

The first method utilizes the drill icons appearing in the top sections of the visualization itself. Choose the arrow icon in the top right section of the visualization to enable drill down. Once the icon is dark, the drill is turned on. 

How to drill down one field at a time? You can select one of the elements in the visualization in a bar chart. See that the title changes as you drill down and back it up again. Choose that icon in the upper right section of the visualization to drill up. 

How to drill down all fields at once? To do this, choose the double arrow on the top left side of your visualization. 

The second method uses the Data/Drill tab from Power BI’s top menu bars.

To turn on drill down, choose a visualization to make it active and from its top menu bars. Click Data/Drill > Drill Down—the icon in the top right section changes to a black background. 

Select Data/Drill > Show Next Level to drill down all fields at once. To drill back up, click Drill/Data > Drill Up.

Conclusion

To sum up, drill down enables you to discover multidimensional data by steering from a general level down to the next level. Take note that drill-down is present in the user interface so users can click on the data representation to unveil their fine levels. Further, drill-down reporting provides the capability to dig deeper into reports for analytical uses.