How to Remove Hierarchy in Power BI Matrix
The matrix visual in Power BI is a workhorse for creating detailed, pivot-table-style reports. But its default behavior of grouping row fields into a "stepped" hierarchy can sometimes be more of a hindrance than a help. If you're aimlessly clicking through menus trying to flatten your matrix into a simple, Excel-like table, you've come to the right place. This tutorial will show you exactly how to remove that hierarchy and get the clean, tabular view you need.
Understanding Why Power BI Creates a Hierarchy
Before we dive into the solution, it’s helpful to understand why Power BI behaves this way. The matrix visual is built for drill-down analysis from the ground up. When you drag multiple fields into the "Rows" well - for instance, Region, then Country, then City - Power BI assumes you want to analyze the data from a high level down to the specifics. It automatically creates a parent-child relationship between these fields, displaying them in a collapsible, indented format.
This "stepped layout" is fantastic when you want to give your report viewers the power to explore the data on their own, expanding and collapsing sections as they wish. You’ll see the familiar plus (+) icons next to parent items (like "North America"), allowing you to drill down to see the countries within it.
However, many reporting scenarios don't require this functionality. You might need to:
Create a simple, flat table for a summary report.
Prepare data for export to Excel or a CSV file where each row needs to be a complete record.
Match the specific layout requested by a manager or stakeholder who prefers a straightforward tabular view.
In these cases, the hierarchy just gets in the way. Thankfully, getting rid of it is a simple, two-step process.
Method 1: Expand the Hierarchy to a Single Level
The first step toward a flat matrix is to fully expand all the levels of your hierarchy. If you don't do this, turning off the stepped layout later won't have the desired effect, as most of your data will still be hidden within the collapsed parent rows.
This is the user action of clicking all the little plus signs at once, so you can see every single row of your intended final report. There are a couple of quick ways to do this.
Step-by-Step Guide Using Visual Icons
Power BI provides dedicated buttons for managing hierarchy levels directly on the visual itself.
Select Your Matrix: Click on your matrix visual to make it active. You’ll see a set of small icons appear at the top-right and sometimes the bottom-right of the visual container.
Locate the Drill-Down Icons: In the header of your matrix visual, look for the drill-down icons. There's an icon with a single down arrow, and another with a "forked" down arrow.
Click the "Expand all down one level" Icon: This is the key icon you need. It looks like a forked arrow pointing downwards. Clicking it once will expand all your top-level categories (e.g., all Regions) to show the next level (e.g., all Countries).
Keep Clicking: Continue to click this same "Expand all" icon until all levels of your hierarchy are visible. As you click, more and more detailed rows will appear. You’ll know you’re done when nothing new appears after you click. All your rows should now be visible, though still in that indented, stepped format.
A Quicker Alternative: Using the Right-Click Menu
For a slightly faster approach that feels more intuitive to some users, you can use the right-click context menu.
Right-Click a Row Header: Hover your mouse over any of the names in the first column of your matrix and right-click.
Navigate to 'Expand': In the menu that appears, find the "Expand" option.
Select 'Expand entire level': Clicking this will expand all the items at that specific level.
Repeat If Necessary: If you have more than two levels (e.g., Region > Country > City), you'll need to right-click on an item in the newly-expanded level (e.g., "United States") and repeat the process by selecting "Expand entire level" again.
Whichever method you choose, the goal is the same: to make sure all your data rows are showing before you move on to the next step. At this point, your matrix will look like a long, indented list, but we're about to fix that.
Method 2: Disable the Stepped Layout for a Flat View
With your hierarchy fully expanded, you're ready for the final step: turning off the stepped layout. This single setting is what transforms your indented matrix into a clean, flat table where each row field gets its very own column.
Finding the Stepped Layout Option
This setting lives in the formatting pane, where all the cosmetic adjustments are made.
Select Your Matrix Again: Make sure your matrix visual is selected.
Open the "Format your visual" Pane: On the right side of the screen, click the icon that looks like a paintbrush to open the formatting options.
Navigate to "Row headers": In the list of formatting sections (like "Visual," "General"), expand the "Row headers" section. Here, you'll find options for fonts, colors, and the +/- icons.
Turn Off the "Stepped layout" Toggle: Scroll down a bit within the "Row headers" section until you see a toggle switch labeled Stepped layout. By default, it's turned on. Simply click this toggle to turn it off.
The moment you turn it off, you'll see your matrix visual transform instantly. The indentation will disappear, and each field you placed in the "Rows" well will now occupy its own distinct column. In our example, you'd now see separate columns for Region, Country, and City.
The parent values, such as "North America," will be repeated on each line corresponding to their child items (e.g., USA, Canada, Mexico). This is the standard, expected appearance of a "flat" or "denormalized" table, and it's perfect for exports and clear, straightforward reports.
Putting It All Together: Pro Tips for a Polished Matrix
Now that you know the core process, here are a few extra tips to fine-tune your new flat matrix and make it even cleaner and more professional.
Turn Off Subtotals
By default, Power BI adds subtotals for each level of your hierarchy. In a flat view, these extra rows can sometimes clutter up the report. You can easily turn them off.
In the Format your visual pane, find the sections for "Row subtotals" and "Column subtotals".
Expand each one and turn the main toggle off. Your report will immediately look less busy. You can also specify if you want subtotals to appear at the top or bottom, which can be useful in some layouts.
Adjust Column Widths
After flattening, Power BI might not perfectly size your new columns. Take a moment to manually resize them by dragging the edges of the column headers, just like you would in Excel, to ensure all your labels are readable without awkward text wrapping or truncation.
Mind Your Naming Conventions
The column headers in your flattened matrix will directly correspond to the names of the fields you dragged in. If you have a field from your data source named DimCustomer_CustomerRegion, that’s what will appear as the header. Double-click the field in the "Rows" well of the Visualizations pane to rename it to something more user-friendly, like "Region". This makes your final table much easier to read.
Common Problems and Considerations
While this process is straightforward, there are a couple of things to keep in mind, especially when working with large volumes of data.
Performance Impact: A flattened matrix explicitly renders every single associated row. If you have hundreds of thousands or millions of rows in your underlying data, forcing Power BI to display all of them at once in a flat table can slow down your report's render time. If you experience lag, try to apply filters before you flatten the matrix to reduce the amount of data being displayed.
Loss of Interactive Drill-Down: A key trade-off is the user experience. By presetting the view to fully expanded, you take away that simple "click the plus button" exploration for the end-user. While the drill-up and drill-down buttons in the visual header will still function, the in-place hierarchy navigation is gone. Be sure this is the right choice for how your audience will consume the report.
Final Thoughts
Transforming a Power BI matrix from its default hierarchical view into a clean, flat table is a simple but powerful technique. By first expanding all levels of the hierarchy and then turning off the "Stepped layout" option in the formatting pane, you can quickly create an organized, Excel-style report that is easy to read, analyze, and export.
While mastering Power BI's formatting options is a valuable skill, digging through menus for a simple setting highlights a common friction point in many data tools. We built Graphed to remove this kind of complexity entirely. Instead of configuring layouts or hierarchies manually, you can describe the report you need in plain English. Just ask "show me sales revenue by region and SKU in a table," and our AI handles building the visualization for you in seconds, connected directly to your live data sources, with no stepped hierarchies to worry about.