Hierarchies and Drill through in Power BI

Every dataset has more to tell than what appears at first glance. A single number showing overall sales or total profit only scratches the surface; the real insights often lie deeper within the layers of the data. For instance, a strong sales figure at the national level may look promising, but once broken down into regions and then into individual cities, the picture can change dramatically highlighting areas of growth as well as spots that need attention.

Power BI makes this layered exploration possible through hierarchies and drill options. A hierarchy arranges related fields, such as Country → State → City, in a logical structure, while drill up and drill down allow seamless movement across these levels. Instead of static charts, visuals become interactive, enabling quick shifts from high-level summaries to detailed breakdowns. This combination not only enhances data storytelling but also makes analysis faster, smarter, and far more engaging.

What is a Hierarchy in Power BI?

A hierarchy is simply a set of fields arranged in logical levels, where each level is a breakdown of the one above it.

Examples:

• Date hierarchy: Year → Quarter → Month → Day
• Geography hierarchy: Region → Country → State → City
• Product hierarchy: Category → Sub-Category → Product

When you use a hierarchy in a visual, users can click to move between these levels instead of creating separate charts for each one.

Sample Data for Demonstration

With this dataset, can build time-based and geographic hierarchies.

Building Hierarchies in Power BI

Date Hierarchy

• Right-click on the Date column → ensure it’s set as Date/Time type.
• Power BI automatically creates a hierarchy: Year → Quarter → Month → Day.

Geography Hierarchy

• In the Data pane, right-click on Region → select Create Hierarchy.

• Right click on Country →  select Add to hierarchy and then choose Region Hierarchy. Similarly, add City to the same hierarchy.

• Rename it as Geography Hierarchy.

When hierarchies are dragged into visuals such as bar charts or line charts, drill options appear.

Ways to access the drill features

There are two ways to access the drill-down, drill-up, and expand features for visuals:

One way is to hover over the visual and use the icons in the action bar. Select the single downward arrow to enable drill-down mode. The grey background shows that drill-down is active.

Another way to access the drill features is by right clicking a data point on the visual to open a menu with available options.

Drill Modes

These are the different drill options available in Power BI. You can check if a visual has a hierarchy by hovering over it. If drill controls show up in the action bar, it means the visual is built on a hierarchy.

Drill Mode	What it Does	How to Identify/Use
Drill Down	Moves to the next level of the hierarchy for the selected data point.	Hover over a visual → Click the single downward arrow. Grey background = active.
Drill Up	Moves back up one level in the hierarchy.	Hover over a visual → Click the upward arrow in the action bar.
Expand Down	Expands to show the next level of the hierarchy while keeping the current level visible.	Hover → Click the double downward arrow.
Show Next Level	Replaces the current view with the next level of the hierarchy for all data.	Hover → Click the forked arrow (two down arrows side by side).

1. Drill Down option

In the first chart, we enabled the Drill Down option by clicking the arrow button at the top of the visual. Once Drill Down was active, we clicked on Asia to move deeper into the hierarchy. As shown in the second chart, the visual updated from showing total Sales by Region to displaying Sales by Country within Asia (India and China), still broken down by City. This step-by-step drill down makes it easy for users to explore data from a broader regional level down to country-level details in a single visual.

2. Expand Down

The Expand All Down option (highlighted at the top of the visual) has been selected to display multiple hierarchy levels at once. Instead of drilling into a single region step by step, the visual expands directly from Region → Country, showing sales for every country across all regions in a single view. Each bar is further broken down by City, making it easy to compare performance not only within a region but also across all countries together. This approach provides a complete picture of detailed data without navigating one level at a time.

3. Show Next Level

The Show Next Level option moves the visual from the current level of the hierarchy to the next level down. For example, if the chart is showing data by Region, selecting this option changes the view to display data by Country. Clicking it again takes the chart from Country to City, and so on. Unlike Drill Down, which requires selecting a specific item, Show Next Level replaces the entire visual with the next hierarchy level for all items at once.

4. Drill Up

The Drill Up option moves the visual back to a higher level of the hierarchy. For example, if the chart is currently displaying data at the Country level, selecting Drill Up changes the view to show data at the Region level. This feature allows navigation upward through the hierarchy to return from detailed views to broader summaries.

Real-World Use Cases

Time Analysis : Explore sales from Year → Quarter → Month → Day.
Geographic Insights : Region → Country → City analysis for global businesses.
Product Analysis : Category → Sub-Category → Product.
Financial Reporting : Consolidated accounts → Departments → Teams.

Best Practices for Using Hierarchies

• Keep hierarchies intuitive and logical. Don’t mix unrelated fields.
• Don’t overload visuals with too many levels – keep 2 to 4 levels max.
• Use tooltips to help users understand where they are in the hierarchy.
• Prefer Expand One Level at a Time for clarity.
• Test performance when using deep hierarchies with large datasets.

Conclusion

Hierarchies and the drill up/down feature in Power BI transform static dashboards into dynamic, interactive reports. They allow users to start at a high-level summary and then drill into the exact detail they need without leaving the page. Whether you’re analyzing time trends, geography, or product performance, hierarchies help tell a complete data story.