How to Remove Total from Matrix in Power BI
The matrix visual in Power BI is a workhorse for summarizing data, acting like a pivot table that lets you group data by rows and columns. But sometimes, the grand total row or column it creates can be more distracting than helpful. This guide will show you exactly how to control and remove those totals so your reports are clean, clear, and show only what you need them to.
Why Would You Want to Remove the Totals Anyway?
Grand totals are often the whole point of a summary table, so why turn them off? While useful, there are several common scenarios where the total row either clutters your visual or, worse, misrepresents your data.
Here are a few reasons you might want to hide the total and subtotal rows:
When Summarizing Averages or Percentages: This is the most common reason. If your matrix shows a metric like Average Sale Price, Conversion Rate, or Profit Margin, summing those values in a grand total row doesn't make logical sense. An average of averages isn't a true average, and a sum of percentages is usually meaningless. Removing the total prevents your audience from drawing incorrect conclusions.
To Improve Visual Clarity: Sometimes, the total row is simply noise. If you're using conditional formatting to create a heat map, the total row can skew the color scale and distract from the patterns in the data you want to highlight. A cleaner, more focused visual is often more impactful.
When Part of a Larger Dashboard: If your Power BI report already has a separate “KPI card” or another visual that clearly displays the grand total, showing it again at the bottom of a matrix is redundant. Removing it saves space and reduces clutter, letting your user focus on the details within the matrix itself.
For Non-Additive Metrics: Some data just can't be added up. Think of things like unique customer counts across different time periods. If a customer made a purchase in both January and February, adding the monthly unique counts would count them twice in the total, which is inaccurate. Removing the total row avoids this kind of misinterpretation.
Deciding whether to keep the total comes down to context. Ask yourself: does this total row add valuable, accurate insight, or does it just create clutter and confusion? If it’s the latter, it's time to hide it.
How to Remove Totals using the "Format Your Visual" Pane
Thankfully, Power BI makes it incredibly simple to toggle the total and subtotal rows on and off. You don't need to write any code or complex formulas, it's all handled with a few clicks in the formatting options.
Let's walk through the exact steps. Imagine you have a matrix showing Total Sales broken down by Product Category (as rows) and Year (as columns).
Step 1: Select Your Matrix Visual
First, click on the matrix visual within your Power BI report canvas. This will activate it, displaying handles around the corners and showing its associated panes on the right side of the screen, namely the "Filters," "Visualizations," and "Data" panes.
Step 2: Open the 'Format Your Visual' Pane
With the matrix selected, look to the "Visualizations" pane. Here, you'll see three icons at the top: the first is for setting up the fields (Rows, Columns, Values), the second looks like a paintbrush and is labeled "Format your visual," and the third is for adding further analytics.
Click the paintbrush icon to open the formatting options. You'll see two tabs here: "Visual" and "General." You want to stay on the "Visual" tab for this task.
Step 3: Find the Subtotals Section
In the list of formatting options under the "Visual" tab, scroll down until you find the section named "Subtotals." It's important to note that Power BI uses the term "Subtotals" here to control both the individual subgroup totals (if you have a hierarchy) and the final grand total at the bottom.
Click on "Subtotals" to expand it. You may see two sections here: "Row subtotals" and "Column subtotals."
Step 4: Turn Off Row and/or Column Totals
This is where you make the change. You now have precise control over which totals appear.
To remove the Grand Total Row:
Under the "Row subtotals" section, you'll see a toggle switch. By default, it's turned on. Simply click the switch to turn it off. As you do this, you will instantly see the "Total" row at the bottom of your matrix disappear from the canvas. Power BI applies these formatting changes in real-time. This same setting also removes any intermediate subtotals if you have a multi-level row hierarchy (e.g., Category > Sub-Category).
To remove the Grand Total Column:
It's the same process for column totals. Under the "Column subtotals" section, click the toggle to switch it to off. This will hide the final "Total" column on the right side of your matrix.
And that’s it! With just a few clicks in the formatting pane, you’ve decluttered your visual and made it much more focused.
A Quick Tip: Fine-Tuning Your Subtotals
Inside the "Subtotals" options, you can also customize the label for your totals. In both the "Row subtotals" and "Column subtotals" dropdowns, you’ll find an option for “Subtotal label.” You can change the default text from "Total" to something more descriptive like "Grand Total," "Overall Sales," or whatever fits your report’s context. This is a small but effective way to make your reports even clearer for your audience.
A More Advanced Approach: Using DAX for Custom Totals
Sometimes, you don't want to completely remove the total row - you just want it to calculate something different than the rows above it. For example, you might want to show the SUM of sales for each individual category, but the AVERAGE of sales in the total row.
This is where Data Analysis Expressions (DAX) come in. With a simple DAX formula, you can tell Power BI exactly how to behave for regular rows versus the total row.
Step 1: Create a New Measure
In the "Data" pane, right-click on the table containing your sales data and select "New measure." This will open the formula bar at the top of the canvas.
Step 2: Write the DAX Formula
We'll use a DAX function called ISINSCOPE() to check if the formula is being calculated for a specific row in your matrix or for the total row. ISINSCOPE([Column Name]) returns TRUE when it's being evaluated for a specific item in that column and FALSE when it’s at the total level for that column hierarchy.
Here’s a practical formula you can use. Let’s say our matrix has the 'Product Category' column on the rows. We want to SUM sales for each category but AVERAGE them in the total row.
Sales (Sum or Avg Total) =
IF(
ISINSCOPE('Products'[Product Category]),
SUM('Sales'[Total Sales]),
AVERAGE('Sales'[Total Sales])
)
Let’s break this down:
IF(...): This is a standard conditional check.ISINSCOPE('Products'[Product Category]): This checks, "Are we currently looking at a single product category?" If yes (like for "Apparel" or "Electronics"), it returns TRUE. If we are in the total row, it's no longer "in scope" of a single category, so it returns FALSE.SUM('Sales'[Total Sales]): This is what the measure calculates if theISINSCOPE()check is TRUE (i.e., for each category row).AVERAGE('Sales'[Total Sales]): This is what the measure calculates if the check is FALSE (i.e., for the grand total row).
Once you’ve entered the formula, press Enter to save your new measure.
Step 3: Use the New Measure in Your Matrix
Now, go back to your matrix visual. In the "Visualizations" pane, drag your original "Total Sales" from the "Values" field and replace it with your newly created measure, "Sales (Sum or Avg Total)."
Your matrix will now display the sum of sales for each product category, but the grand total row at the bottom will show the average sales across all categories, giving you a custom-tailored calculation that a standard total couldn’t achieve.
This method offers incredible flexibility and is a great way to elevate your reports from standard summaries to truly insightful analyses.
Final Thoughts
Controlling the display of totals in a Power BI matrix is a fundamental formatting skill that dramatically improves the clarity and accuracy of your reports. Whether you’re quickly toggling them off in the format pane to clean up a visual or writing a custom DAX measure to change how the total is calculated, you now have the tools to make your data presentations precise and professional.
We built Graphed because we believe getting insights from your data shouldn't require you to become an expert in the complex menus and DAX syntaxes of traditional BI tools. Instead of digging through formatting panes, you could simply tell our AI, "Show me a table of sales by product category for last year and don't include a total row." We handle all the technical setup in the background, allowing you to move directly from question to answer in seconds.