How to Drill Down in Power BI Matrix Table

A Power BI matrix can look like a simple table, but its real power lies in its ability to let you explore your data. Drill-down functionality is the feature that unlocks this potential, turning a static summary into an interactive analytical tool. This article will guide you through exactly how to set up and use the drill-down features in a Power BI matrix to uncover insights hidden in your data.

What Exactly is "Drill Down" in Power BI?

Drill down is an interactive feature that allows you to explore multiple levels of hierarchical data within a single visual. Imagine you have a matrix showing total sales by product category. It’s useful, but it’s a high-level view. With drill down, you can click on a specific category, like "Electronics," to reveal the sub-categories within it, such as "Smartphones," "Laptops," and "Headphones." You can then click on "Smartphones" to see the individual product sales in that sub-category. Essentially, you're "drilling down" from a broad summary to a more granular view.

This is crucial for good data analysis because it lets you answer follow-up questions without building a dozen different reports. You can quickly investigate why one category is outperforming another or diagnose a problem at its source by moving from a summary number to the detailed data behind it.

Step 1: Setting Up Your Data Hierarchy

Before you can drill down, you need to tell Power BI how your data is related. This is done by creating a hierarchy. A hierarchy is just a logical ordering of your fields from the broadest category to the most specific. Think of it like a family tree for your data.

For example, a common e-commerce sales hierarchy might be:

• Level 1: Product Category (e.g., Clothing, Electronics, Home Goods)
• Level 2: Product Sub-Category (e.g., Shirts, Phones, Kitchenware)
• Level 3: Product Name (e.g., Men's V-Neck Tee, Pixel 8 Pro, 12-Piece Cookware Set)

You can create a hierarchy in Power BI's Data model view, but the simplest way is to do it directly in the Report view's "Data" pane.

How to Create a Hierarchy

To create a hierarchy, you simply drag one field on top of another in the Data pane on the right side of the screen.

1. Find the field you want to be at the top of your hierarchy (e.g., Product Category).
2. Find the field for the next level down (e.g., Product Sub-Category).
3. Click and drag the Product Sub-Category field and drop it directly onto the Product Category field.

Power BI will automatically create a new hierarchy named "[Top Field] Hierarchy" (e.g., "Product Category Hierarchy"). You can rename it by clicking the three dots (...) and selecting "Rename." To add more levels, just drag other fields and drop them into the hierarchy you just created.

Step 2: Building Your Matrix and Adding the Hierarchy

Once your hierarchy is ready, you can build the matrix visual.

1. Select the Matrix Visual: In the "Visualizations" pane, click the icon for the Matrix visual.
2. Add the Hierarchy: Drag the entire hierarchy you created from the "Data" pane and drop it into the "Rows" field in the "Visualizations" pane. Dragging the whole hierarchy at once is much faster than adding each field individually.
3. Add Your Values: Drag the numerical field you want to analyze, like Sales Revenue or Units Sold, into the "Values" field.
4. Add Columns (Optional): If you want to segment your data further, you can add a field like Year or Region to the "Columns" field. For this example, we'll keep it simple with just rows and values.

At this point, you'll see a matrix that shows only the top level of your hierarchy (e.g., Sales by Product Category).

Step 3: Using the Drill-Down Buttons

When you select the matrix visual, you'll notice a few small arrow icons appear at the top-right corner. These are your controls for drilling down and up through your data hierarchy.

Turn on Drill Down Mode (Single Downward Arrow)

This mode lets you drill down into one specific row at a time. It feels like navigating through folders on a computer.

• How to use it: First, click the single downward arrow icon to enable "Drill Mode." The icon will turn dark to show it's active. Now, click on a row header in your matrix, like "Clothing." The matrix will update to show only the sub-categories within "Clothing" (e.g., "Shirts," "Pants," "Dresses"). Click "Shirts" to see the individual product names.
• When to use it: Use this when you want to investigate a single specific category without getting distracted by others. It’s perfect for focused analysis.

Go to the Next Level in the Hierarchy (Double Downward Arrow)

This option expands all rows to the next level of the hierarchy simultaneously. It gives you a complete view of the next level for everything.

• How to use it: Click the double downward arrow icon. Instead of showing just product categories, the matrix will now show all product sub-categories for every category. If you click it again, it will move down another level to show every single product name.
• When to use it: This is useful when you want to compare items across different parent categories at the same level - for example, comparing the performance of "Smartphones" vs. "Laptops."

Expand All Down One Level in the Hierarchy (Forked Arrow)

This one is probably the most useful and also the most commonly misunderstood. It expands all items to the next level but importantly, it keeps the parent-level labels visible. This provides valuable context.

• How to use it: Click the forked arrow icon. You will now see both the category and sub-category. For example, your rows might look like this:
• When to use it: This is the best option for creating reports that are easy to read and understand. Viewers can see the breakdown while also remembering the parent category each item belongs to. The "double arrow" can be confusing because once you drill down, you lose the context of the original parent category.

Drill Up (Upward Arrow)

As you might guess, the upward arrow lets you move back up the hierarchy one level at a time. If you’ve drilled all the way down to product names, clicking "Drill Up" will take you back to sub-categories, and clicking it again will take you back to the top-level categories.

Using the Right-Click Menu

You don't always have to use the icons at the top of the visual. You can also right-click on any row header in the matrix to bring up a context menu with drill-down options:

• Drill Down: This is the same as turning on Drill Mode and clicking that specific row. It navigates to the next level for only the row you selected.
• Show next level: Behaves like the "Double Downward Arrow," showing everything at the next level of the hierarchy.
• Expand to next level: Behaves like the "Forked Arrow," expanding the selected row while keeping the parent label.

Using the right-click menu can often feel more intuitive and faster once you get the hang of it.

Tips for Better Drill-Down Reports

• Use the Indented Layout: In the Format Visual pane, go to "Row headers" -> "Options" -> and turn on "Stepped layout." This will turn off the default indented view. Further down in that section, look for the +/- icons setting. Turning these on adds a small plus or minus sign next to each row, similar to how you browse files in Windows Explorer. Users find this very intuitive for expanding and collapsing rows and often prefer it to the main drill-down buttons.
• Keep Hierarchies Logical and Lean: Don’t create hierarchies that are 10 levels deep. It can slow down your report and create a confusing experience. Stick to 3-5 logical levels that guide a user from a broad summary to useful detail.
• Educate Your End-Users: Not everyone knows that Power BI visuals are interactive. Add a small text box or edit the chart title to hint that users can click to "drill down for more detail." A small bit of instruction can dramatically increase how much people use and value your reports.

Final Thoughts

Mastering the drill-down functionality in Power BI's matrix transforms your reports from static information dumps into dynamic tools for discovery. Moving between high-level summaries and granular details allows you to spot trends, identify outliers, and answer critical business questions much more efficiently.

While Power BI offers powerful deep-dive capabilities, sometimes the process of setting up hierarchies and navigating reports can still feel like manual work. That's why we built Graphed to simplify the entire analytical workflow. Instead of clicking through menus to drill down, you can just ask questions in plain English, like "Show me sales by product category" and follow up with "break down Electronics by sub-category." We connect your data sources in seconds and use AI to build dashboards and answer questions, turning hours of data wrangling into a simple conversation.