MrExcel.com - Your One Stop for Excel Tips & Solutions

Enable Drill-Down Mode

April 12, 2023 - by

Enable Drill-Down Mode

If the data model includes a hierarchy, you can turn on drill-down mode.

The Power BI team must love using tiny subtle icons, because Power BI is full of them. In the top right of a chart, this icon turns on Drill-down mode.

Hover over a visual and four icons appear in the top right. A down-arrow offers Click to Turn on Drill Down.
Figure 1081. This icon appears when there is a hierarchy in the data.

When you click on the East region, you will see a chart with just the states in the East.

In the top-left of the chart are three icons related to drill-down.

  • The first icon goes up one level. Click this to return to all regions after view states.
  • The second icon moves down one level in the hierarchy without filtering to one region. Click this icon to move from a Region report to a State report. Instead of seeing only states in the East region, you will see all states.
  • The third icon is Expand All One Level Down. Click this while viewing the East Region states to see all East Region States and Cities.
Click on the East region column and the column chart changes to show states in the East: NY, NJ, NC, FL, and CT.
Figure 1082. Expand the current selection to include cities.
Click on Drill Down One Level and you see the cities in those states.
Figure 1083. Instead of listing White Plains, Kenilworth, Murray Hill, you see both State and City.

Problem: How can I sort the data in the chart?

Strategy: Click the elipsis in the top-right corner of the chart. Options let you sort the chart by alphabetic labels or high-to-low by numeric values.

The fourth icon in the top right of the visual is three dots. It offers Sort by State, Sort by Profit, or Export Data.
Figure 1084. Sorting is hidden behing the … in the top right of the chart.

This article is an excerpt from Power Excel With MrExcel

Title photo by kaleb tapp on Unsplash