How to Remove Row Subtotals in Power BI

Power BI's automatic subtotals are great for getting a quick summary in a table or matrix, but sometimes they create more visual clutter than clarity. If you're tired of fighting with default settings to create a clean, streamlined report, you've come to the right place. This guide will walk you through the simple, built-in ways to remove row subtotals and even show you a trick for more advanced control.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Would You Want to Remove Row Subtotals?

While subtotals can be helpful for hierarchical data, there are many common scenarios where they just get in the way. Removing them is often a matter of good report design and clear data presentation.

• Reducing Clutter: The most common reason is simply to clean up your visual. A long matrix with multiple row levels can quickly become a dense forest of bolded numbers. Removing subtotals makes the detailed data easier to read at a glance.
• Avoiding Redundant Information: In some cases, a subtotal might be identical to the "Grand Total" if you only have one main category, making it feel pointless and repetitive.
• When Averages Don't Aggregate: Subtotals are often a sum, which makes sense for metrics like sales or volume. But if your values are percentages, averages, or ratios, a summed subtotal can be meaningless or misleading. For example, summing up your profit margin percentage by region doesn’t give you your total profit margin.
• Stakeholder Preferences: Sometimes, the people reading your report just want to see the raw, detailed rows. They may prefer to perform their own high-level analysis and find the extra summary lines distracting.

Whatever your reason, taking control of these summary rows is a fundamental skill for creating professional-looking Power BI reports.

Method 1: Using the Format Visual Pane (The Easiest Way)

For 95% of cases, the solution is just a few clicks away in the formatting options. Power BI gives you straightforward tools to control subtotals without writing a single line of code.

Let's assume you have a matrix with a row hierarchy, like "Category" and then "Sub-Category" breakdown for your products.

Step-by-Step Instructions to Remove All Row Subtotals:

1. Select your visual: First, click on the matrix or table visual on your report canvas to activate it. You'll see the border highlighted, and the associated data panes will become active.
2. Open the 'Format visual' pane: Look for the three main icons next to your visualization: 'Add data to your visual,' 'Format visual,' and 'Add further analysis...' Click on the middle icon, which looks like a paintbrush, to open the formatting options.
3. Find the 'Row subtotals' section: Scroll down the list of formatting options. You should see a section labeled "Row subtotals." Click to expand it if it isn't already.
4. Flip the switch: The very first option in this section is a simple on/off toggle for all row subtotals. Just click the switch to turn it "Off." Immediately, you'll see all the subtotal rows disappear from your matrix, leaving only the detail rows and the Grand Total.

And that's it! In four simple steps, you’ve cleaned up your visual and removed the clutter.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Fine-Tuning: Removing Subtotals for a Specific Level

But what if you have multiple levels in your hierarchy and only want to remove some subtotals? For example, you might have Region -> Country -> City and want to see the subtotal for each Region, but not for each Country.

Power BI has you covered here as well. Instead of using the master on/off switch, you can control each level independently.

1. Follow steps 1-3 from the previous section to navigate to the 'Row subtotals' menu.
2. Make sure the main 'Row subtotals' toggle is switched On.
3. Look for the setting labeled "Per row level." Flip that switch to "On."
4. Once activated, you will see your row hierarchy fields appear (e.g., Category, Sub-Category). Each field will have its own on/off switch.
5. Now you can simply turn off the subtotal for the exact level you want to hide. In our example, you could turn off the Category subtotal but keep the main one for a larger group.

This "Per row level" option gives you incredible flexibility, allowing you to tailor your matrix to the precise level of detail your audience needs.

Method 2: A DAX Workaround for Conditional Subtotals

Sometimes just turning subtotals on or off isn't enough. What if you want to show a value in the subtotal row, but want it to be a different calculation? For example, instead of the SUM of sales, you might want to show the AVERAGE sale amount at the subtotal level. Or maybe you want to show a subtotal for one category but hide it for another.

For these advanced cases, we can turn to DAX (Data Analysis Expressions). By creating a clever measure, you can completely control what appears in both the detail and subtotal rows.

The key is to use a DAX function that can detect whether the calculation is happening on a regular row or a subtotal row. The most common functions for this are HASONEVALUE() or ISFILTERED().

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example: Showing an Average for Subtotals Instead of a Sum

Let's say you have a measure that calculates total sales:

Total Sales = SUM('Sales'[Revenue])

In a matrix with Category and Sub-Category, the subtotal for Category will be a SUM of all Sub-Category revenues. To change this, you could write a new measure like this:

Sales with Average Subtotal = 
IF(
    ISINSCOPE('Products'[Sub-Category]),
    SUM('Sales'[Revenue]),
    AVERAGEX(VALUES('Products'[Category]), [Total Sales])
)

Let's break down what's happening here:

• ISINSCOPE('Products'[Sub-Category]): This function checks if we are currently looking at a unique Sub-Category level. It returns TRUE for the detailed rows and FALSE for the Category subtotal row (where multiple sub-categories are being aggregated).
• If TRUE (it's a detail row): The measure simply returns the normal SUM('Sales'[Revenue]).
• If FALSE (it's a subtotal row): The measure calculates an AVERAGEX of the Total Sales across all the sub-categories within that category.

Example: Hiding a Subtotal Completely with DAX

You can also use this pattern to just hide a subtotal by returning a BLANK() value.

Sales with Hidden Subtotal = 
IF(
    HASONEVALUE('Products'[Sub-Category]), 
    SUM('Sales'[Revenue]), 
    BLANK()
)

Here's how this logic works:

• HASONEVALUE('Products'[Sub-Category]): This function asks, "Is there only one sub-category value in the current filter context?" For the detail rows, the answer is "yes." But for the subtotal row that aggregates multiple sub-categories, the answer is "no."
• When you use this measure in your matrix (and remove the original 'Total Sales' field), it will show the sales amount on the detailed rows and a completely blank cell where the subtotal used to be.

This DAX method is more involved, but it unlocks a level of customization that the Formatting Pane can't offer and is essential for highly tailored financial or operational reports.

Quick Troubleshooting: Common Problems

Even with simple features, you can occasionally run into minor issues. Here are a couple of common sticking points and how to resolve them.

"I turned off subtotals, but they are still showing!"

This almost always happens when you've been experimenting with the "Per row level" setting. Double-check that section in the Format Pane. If Per row level is on, the master toggle is ignored. You either need to turn off the individual toggles for each level or just turn off the 'Per row level' option altogether and use the master switch.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

"How do I remove the Grand Total row at the bottom?"

Excellent question! Power BI considers the "Grand Total" to be separate from "Row Subtotals." Look in the same Format Visual pane for a section called "Totals." Inside, you’ll find a separate on/off toggle for both the 'Row grand total' and 'Column grand total.' You can turn this off without affecting your subtotals.

Final Thoughts

Removing row subtotals in Power BI is a quick formatting adjustment that gives you immediate control over your report's layout. While the one-click solution in the Format pane handles most situations, learning to use DAX functions like ISINSCOPE or HASONEVALUE gives you the ultimate flexibility to create truly custom summary visuals.

Mastering tools like Power BI can be powerful, but it often involves a steep learning curve and wrestling with formatting menus and DAX formulas. At Graphed, we've focused on taking all that manual work out of the equation. We provide an AI data analyst that connects to your marketing and sales data sources, allowing you to build real-time dashboards simply by asking for what you need in plain English. Instead of digging through menus, you can just ask, "Show me my sales by product sub-category in a table, without subtotals," and Graphed instantly builds it for you.