Graphed LogoGraphed
Login

How to Drill Down in Power BI

Cody Schneider•

Drilling down in Power BI is one of its most powerful interactive features, turning a simple chart into an explorable story. Instead of just showing top-level numbers, you can click into your data to uncover the details hidden beneath the surface. This article will show you exactly how to set up hierarchies and use the drill-down features to transform your static reports into dynamic analytical tools.

What is a Drill Down in Power BI?

At its core, a drill down is the process of navigating from a summarized, high-level view of your data to a more detailed, granular level within the same visualization. Think of it like a set of nested folders on your computer. You start at the main "Sales" folder, then click to open "Sales by Region," then click on a specific region to see "Sales by City," and finally click on a city to view "Sales by Store."

It’s all part of the same exploration process, allowing you and your report viewers to peel back layers of data and ask follow-up questions without needing to create a dozen different charts. You might see that overall sales are up for the year, but with drill down, you can investigate which specific quarter, month, or product category is driving that growth.

Drill Down vs. Drill Through: What’s the Difference?

While often mentioned together, these two features serve different purposes.

  • Drill Down: This happens within the same visual. You are moving up and down between different levels of a pre-defined hierarchy (e.g., Year → Quarter → Month). The visual itself changes to show the next level of detail.

  • Drill Through: This navigational action takes you from one data point on a report page to a completely different report page, which is filtered to show details about the specific data point you selected. For example, you could right-click on "California" on a map chart and "drill through" to a separate report page dedicated to California’s detailed performance metrics.

This tutorial focuses specifically on the drill-down feature, which is perfect for hierarchical data exploration in a single chart.

How to Set Up a Drill Down Hierarchy

Enabling drill down in a Power BI visual is surprisingly simple. It all comes down to creating a hierarchy - an ordered stack of data fields that you want to explore. Let's walk through the steps.

Step 1: Choose and Create Your Visual

Drill downs work best with visuals that can display categorical data, like column charts, bar charts, line charts, pie charts, and treemaps. For this example, let's start with a standard Clustered column chart, as it’s one of the most common and an ideal choice for this feature.

First, create your base chart with a single category and a single value. For instance, you could show 'Total Sales' by 'Product Category'.

Step 2: Build Your Hierarchy

This is where the magic happens. A hierarchy is just a logical ordering of fields from general to specific. You create it directly within the visualization pane by dragging and dropping fields into the axis or category well.

Imagine you have the following data fields related to products:

  • Category (e.g., Biking, Camping, Hiking)

  • Sub-Category (e.g., Mountain Bikes, Tents, Boots)

  • Product Name (e.g., Trek Marlin 5, Coleman Sundome, Merrell Moab 3)

The logical hierarchy here is Category > Sub-Category > Product Name.

To build this in your visual, follow these steps:

  1. Drag the highest-level field, Category, into the X-axis well of your column chart.

  2. Next, drag the Sub-Category field into the same X-axis well, placing it directly underneath Category.

  3. Finally, drag the Product Name field into the X-axis well, placing it underneath Sub-Category.

As you add fields under one another in the same data role (like the X-axis), Power BI automatically creates a hierarchy. You've now told your chart that there are layers to explore! Once you've created a hierarchy, your visual has multiple data levels. Power BI will display on-hover tool-tips and buttons you can use for your analysis in the header of the visualization.

Using the Drill Down and Drill Up Features

Once your hierarchy is set, Power BI activates a set of interactive arrows in the visual's header, typically in the top-right corner. Understanding what each button does is the key to mastering your data exploration.

1. Turn on Drill Mode (Single Down Arrow)

This is the most direct way to investigate a specific data point. First, you have to click on the button to activate it. The button will have a dark background when it is activated.

  • How to use it: Click the downward arrow to activate "Drill Mode." Then, click on a specific data point in your chart (like the "Biking" column). The chart will update to show only the next level of data for that selection (e.g., the sub-categories within "Biking").

  • When to use it: Use this when you want to focus your investigation on a single piece of the pie. For example, if "Camping" sales look unusually high, you can drill in to see which sub-categories of camping gear are responsible.

2. Go to the Next Level in the Hierarchy (Double Down Arrows)

This option expands everything at once and moves to the next level of your report without preserving any context from the previous view of the data. Your chart view will transition completely and just show your data through the lens of the next dimension in your reporting hierarchy.

  • How to use it: Simply click the button with the two downward arrows. If your chart originally shows data by Category, clicking this button will change it to show data for all Sub-Categories, unfiltered.

  • When to use it: This is useful when you want to shift your perspective from a high level to the next granular level for all your data, not just one specific part.

3. Expand All Down One Level (Forked Down Arrow)

Similar to the above, this option expands all the categories, but as its name suggests, this button expands your view to include more detailed context in the visualization.

  • How to use it: Click the forked arrow. If you're at the Category level, this option will display all the Sub-Categories, but it will also concatenate the labels to show the parent. For example, you might see "Biking - Mountain Bikes" and "Camping - Tents."

  • When to use it: This provides an excellent view with full context. It's perfect for when you want to see the performance of a sub-category while still being reminded of the parent category it belongs to, all in one view.

4. Drill Up (Single Up Arrow)

It's easy to guess that the “Drill Up” button functions as our primary option to move back one level. After you've drilled down into your data, you'll need a way back. That's what the "Drill Up" feature allows you to accomplish, it lets you go a step back and move to a less granular level.

  • How to use it: Just click the up arrow, and it takes you back by your step within your reporting hierarchy and reverts your report by one step. You can click it multiple times and move back multiple layers in your hierarchy.

Practical Example: Building An Interactive Sales Report

Let's create a practical example using sales data:

The Goal: Answer the following question about our sales performance with a Power BI report: "How well did our 'E-bikes' do year-over-year, and what was our best-performing product SKU that drove revenue this year?"

We'll create a report that will provide all stakeholders with the exact answer they are looking for, with two clicks without an extra chart clogging our report view.

  • Fields:

    • Dimensions (The "who," "where," "what?")

      • Product Category

      • Sales Date (Power BI automatically converts it into a Year, Quarter, Month, Day hierarchy)

    • Metric (The 'how much?')

      • Sales Sum

The Final Hierarchy (Our Analysis Path): Sales Year > Product Category > Sales Quarter

Step 1: Add a visualization:

Go to the Visualization panel and click on “Line And Clustered Column Chart” and drag it on the new report canvas.

Step 2: Set up your chart:

  • Drag 'OrderDate' to the X-axis

  • Drag 'Sales Category' to the X-axis as well

  • Move 'OrderDate' to be the first in our Shared axis hierarchy

  • Drag 'Sales Amount' to the Y-axis

Now we have a fully-functional drill-down chart built. Double-click the title and change it to 'Sales Performance By Category'.

Now we can move to reporting and share with our manager the following: 'To view year-over-year sales trends, you can select any product, click on the right-hand arrow, and you'll get quarterly data.' This is a great deliverable for any meeting.

If we need to provide a high-level view, we are also now able to offer one. For example, to see how our sales for all products look quarter-over-quarter, click the arrow icon on the visual and then click the forked down arrow (Expand-all-down icon). This is how your chart should look.

Tips for Better Drill Downs

Here are a few useful guidelines on how to design your Power BI reports that will leverage its great capabilities:

  • Think of your user audience: Build hierarchies based on how users think by business questions, not just adding as many dimensions as you can.

  • Use Self-descriptive titles: Your chart's title should guide your users on how to start, with a prompt such as "Click on each column to explore this year's Sales." Let your titles set viewers up on a path of self-exploration of data.

  • Keep it simple. It's very easy to create a 37-step drill-down path, but they get really confusing to navigate. Trim down and focus your analysis path to a maximum of five steps.

Final Thoughts

The drill-down functionality in Power BI is a total game-changer, swapping static, one-dimensional view reports for true, interactive exploration tool dashboards. Setting up a meaningful data relationship hierarchy not only saves you reporting space and keeps visualizations clean but also empowers all end-users to explore your data and answer those questions themselves.

Learning how to create dashboards on reporting platforms like Power BI is certainly a challenge on its own. That's why at Graphed we have created a platform that builds dashboards without any learning curve. You just connect your data sources and then describe the dashboards you want to visualize to start your process, from data to insights faster than ever. Graphed automates that entire end-to-end process, freeing up your team's precious time to focus on insights instead of manually building those time-consuming reports from scratch.