Graphed LogoGraphed
Login

How to Remove Total from Power BI Table

Cody Schneider•

While the automatic "Total" row in a Power BI table is often helpful for a quick summary, sometimes it just gets in the way. Whether it’s cluttering a clean design, confusing the narrative of your report, or showing a meaningless total for non-additive metrics, knowing how to remove it is an essential skill. This guide will walk you through a few different ways to remove the total from your Power BI tables and matrices, from a simple one-click solution to a more flexible DAX approach.

Why Remove the Total Row in Power BI?

Before jumping into the "how," it's worth understanding the "why." You might want to remove the total row for several practical reasons:

  • Clarity and Focus: Sometimes, the main purpose of a table is to show a detailed list of items, like customer names, product SKUs, or blog post titles. A "total" for these categories is often meaningless and distracts from the individual line items.

  • Better Visual Design: Reports and dashboards are often designed to be clean and minimalistic. An extra total row can clutter the visual and take up valuable space, especially if that summary information is already presented elsewhere, like in a card visual.

  • Avoiding Misleading Metrics: Totaling certain values can be mathematically incorrect or misleading. For example, calculating a grand total of "Average Order Value," "Discount Percentage," or "Customer Ratings" rarely provides a useful or accurate business insight. In these cases, it's better to remove the total to prevent misinterpretation.

  • Specific Reporting Needs: You might be creating a detailed report for stakeholders who only want to see the performance of individual segments, not the aggregate across all of them. Removing the total keeps the focus exactly where it needs to be.

The Easiest Method: Using the Formatting Pane

For most situations, the quickest way to remove the total row is directly within the visual's formatting options. It only takes a few clicks and is the best place to start. This works for both Table and Matrix visuals, although the options might look slightly different.

Step-by-Step Instructions:

  1. Select Your Visual: Start by clicking on the table or matrix visual on your Power BI canvas. You’ll know it’s selected when you see a border appear around it and the "Data" and "Format your visual" panes update on the right side of the screen.

  2. Open the "Format Your Visual" Pane: On the right-hand side, click on the paintbrush icon to open the formatting options for your selected visual.

  3. Navigate to Totals: Scroll down the list of formatting options until you find the "Totals" section. Click on it to expand the available settings.

  4. Toggle It Off: The very first option you'll see is a master toggle switch. Simply click this toggle to the "Off" position. You will immediately see the "Total" row disappear from your table.

For a Matrix visual, you'll see separate toggles for "Row subtotals" and "Column subtotals." You can turn off either or both, depending on what you need to show in your report. Disabling both will remove all totals from your visualization.

That's it! In most cases, this simple action is all you need to clean up your visual and achieve the look you want.

More Control: Removing Totals for Specific Columns in a Matrix

What if you want to show a grand total for your "Sales Revenue" column but not for your "Average Discount" column in the same matrix? Calculating a sum of averages is usually bad practice, so you'll want to remove it. Power BI's Matrix visual gives you the granular control to do just this.

Here is how to fine-tune your column totals:

  1. Select Your Matrix: Click on your matrix visual to select it.

  2. Go to the Formatting Pane: Click the paintbrush icon to open the formatting options.

  3. Open Column Subtotals: Scroll down and expand the "Column subtotals" section.

  4. Enable "Per Column Level": You'll see an option called "Apply to settings." By default, it's set to "All column levels." Click the toggle for "Per column level" to "On."

  5. Customize Totals for Each Column: Once you enable "Per column level," you can use the "Total for series" dropdown to pick which data column to format. From the dropdown list, select the column where you want to remove the total (e.g., "Average Discount"). Then, simply switch the "Total" toggle for that specific series to "Off."

Now, your matrix will still show the grand total for your revenue but will have a blank space under the average discount column, leading to a much cleaner and more mathematically sound report.

The Power User Method: Using DAX to Control Totals

While the formatting pane is fast and convenient, there are times you need more control than a simple toggle can offer. Perhaps you want the total row to display a different calculation entirely, or you want it to be blank based on a specific logical condition. This is where DAX (Data Analysis Expressions) comes in.

The key to controlling totals with DAX lies in understanding the "filter context." A regular row in a table has a specific context (e.g., this row is for "Product A"), but the total row has no specific context — it represents all products. We can leverage this distinction using a DAX function called HASONEVALUE().

The HASONEVALUE() function checks if a column has been filtered down to a single, distinct value. In the context of a table visual:

  • For a regular data row, HASONEVALUE() on the category column will return TRUE.

  • For the "Total" row, HASONEVALUE() will return FALSE, because it operates on all the values in that column.

Step-by-Step DAX Example

Let's say you have a table showing sales revenue by product. The formatting toggle would completely remove the total. But if you use DAX, you can simply make the total an invisible BLANK(), offering more flexibility for other calculations if needed.

  1. Create a New Measure: Right-click on your table in the "Data" pane and select "New measure."

  2. Write the DAX Formula: In the formula bar, enter the following DAX expression. Let's assume your table with sales figures is named 'Sales' and the column with product names is 'Products'[Product Name].

  1. Break down the formula:

  • IF(...): Logical test.

  • HASONEVALUE(Products[Product Name]): Checks if the current context is for a single product.

  • SUM(Sales[Revenue]): When true, sum the revenue for that product.

  • BLANK(): When false (total row), show nothing.

  1. Use the New Measure in Your Table: Now, go back to your table visual. Remove the original "Revenue" field and drag in the new "Revenue (No Total)" measure.

Your table will now display revenue per product, but the grand total row will be blank, effectively removing it from view.

Common Questions and Best Practices

My Totals Don't Add Up Correctly Anyway

Sometimes you’ll build a visual and notice the total is wrong. This is common when working with complex data or measures that shouldn't just be summed up. For example, if you have a measure calculating "Average Sales per Customer," the total row will often show the average of the averages — not the true overall average. Using a DAX formula with functions like SUMX or AVERAGEX over the correct table can fix this, giving you accurate and trustworthy totals. This level of control is where DAX truly shines.

What's the Difference Between a Table and a Matrix?

While similar, a Table is a flat, two-dimensional grid of rows and columns. It has a single "Totals" section in the formatting pane. A Matrix is more like a pivot table, allowing you to have rows, columns, and sub-groupings. Because of this added complexity, it has separate formatting controls for "Row subtotals" and "Column subtotals," giving you a lot more configuration power.

Performance Considerations

Simply toggling the totals in the formatting pane has virtually no impact on your report's performance. Using DAX measures with HASONEVALUE is also extremely efficient and won't slow down your reports, even on large datasets. The performance impact only becomes a concern when you write extremely performance-intensive DAX that has to iterate over millions of rows for every calculation.

Final Thoughts

There are multiple ways to remove totals from a Power BI table, from simple format toggles to flexible DAX measures. For most users, disabling the "Totals" option in the formatting pane is sufficient. For more fine-tuned reports, especially with matrices, using the "Per column level" setting provides great control. And for ultimate flexibility and to ensure proper calculations, mastering a simple DAX pattern with HASONEVALUE unlocks complete control over how your tables and matrices behave.

While mastering tools like Power BI is incredibly rewarding, there’s no denying the sometimes steep learning curve associated with things like DAX formulas and nested formatting panes. The dream for most of us is to just describe what we need and have it appear. With our tool, Graphed, you can do just that. We enable you to create live-updating charts and reports using simple, natural language. Instead of clicking through menus, you can simply ask, "show me a table of revenue by product but without a total row," and we’ll build the visual for you, instantly. It’s the easiest way to get from data to insights without getting bogged down in technical details.