How to Explore Drilling Down and Drill-Through in Power BI  -  AI and Power BI  - Chapter 4

What is special in Power BI with Drill Down/Up, Drill-Through and Expand/Collapse?

Drilling down or up is a very interesting feature in any business intelligence tool. Power BI has the same feature but with some twist.

In this blog, we are going to explore the below points

  1. Expand and Collapse features in a visual
  2. Drilling Up and Down features in a visual
  3. Drill Through for a measure, for a column

Get Data

For this case study, I consider the US Superstore dataset from Kaggle.

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
Image by Author
  • Click on Load and save data.

Create Hierarchies

Let us create one hierarchy for this case study. 

Under the Report section, click on Category and select the option Create Hierarchy

Hierarchies in Power BI
Image by Author

Expand and Collapse features in a Visual

To explore expand and collapse features in a visual, you can create two different types of visuals. 

  1. Create one measure of Total Sales. 

Total Sales = SUM(‘US Superstore data’[Sales])

2. Follow the below steps to create Matrix Visual.

Expand and Collapse in a Power BI Visual
Image by Author

3. Now you can explore collapse and expand options of this visual.

Expand and Collapse in a Power BI Visual
Image by Author
Expand and Collapse in a Power BI Visual
Image by Author

4. This similar experiment can be implemented using one hierarchy.

Expand and Collapse in a Power BI Visual
Image by Author

Advantage: With this, you achieve a detailed report with sums including all available levels.

Drilling Up and Down features in a visual

  1. If you right-click on any visual, it allows you to either Drill Down or Drill Up, based on which level of hierarchy you are in.
  2. Difference between Drilling and Expanding/Collapsing: Drilling down allows to the next level and it limits all the other rows. That means only values under the selected level will display. Then Drilling Up allows you to just collapse the values to the 
Drilling Up and Down features in Power BI visual
Image by Author
Drilling Up and Down features in Power BI visual
Image by Author

3. In the above visual, you can observe below items

  • one single arrow pointing up
  • one single arrow pointing down
  • a double arrow pointing down
  • a fork pointing down
Drilling Up and Down features in Power BI visual
Image by Author

Drill-through

In Power BI, Drill-through is available for a measure, for a column, or for a different report.

Report navigation is one of the important parts of any analytics project. From a summary report, you can navigate to one detailed report which can provide more detailed level insightful information. 

 Drill-Through For a Measure

  1. First, you create one Sales Detail report with Category, Sub-Category, Order ID, Total Sales, Profit, Quantity.
  2. To make this report a drill-through report, you need to add one measure “Total Sales” into the Drill-through section in the Visualization pane.
  3. Now this report will be available in the Drill-through context menu for this measure. If the measure is filtered, then it will be automatically reflected in the detailed report.
Drill-Through For a Measure in power bi
Image by Author — Sales Detail Report

Drill-Through for a Column

  1. First, you create one Product Detail report with Category, Sub-Category, Product Name, Total Sales.
  2. To make this report a drill-through report, you need to add one dimension column “ Category” into the Drill-through section in the Visualization pane and enable the option for “Keep all filters”.
  3. Now this report will be available in the Drill-through context menu for this column.
Drill-Through For a column in power bi
Image by Author — Product Detail Report

Navigation from Summary Report

Now it is time to watch, how you can navigate to a Drill-through report from a summary report. 

  1. Go to Summary Report “Report with Hierarchy” 
  2. Right Click on a Dimension Column “Category”
  3. Go to Drill through option from the pop-up menu
  4. Click on “Product Detail Report”
Drill through report in Power BI
Image by Author
  1. Go to Summary Report “Report with Hierarchy”
  2. Right Click on Measure “Total Sales”
  3. Go to Drill through option from the pop-up menu
  4. Displayed two detail reports — Sales Detail Report, Product Detail Report
  5. Click on “Sales Detail Report”
Drill through report in Power BI
Image by Author

Download

Please find the code in the below location

AI and Power BI — Chapter 4

Video

Conclusion

In this blog, we understand the below items

  1. Expand and Collapse features in a visual
  2. Drilling Up and Down features in a visual
  3. Drill Through for a measure, for a column.

In my next blog, we will learn more about AI and Power BI.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: