Graphed LogoGraphed
Login

How to Show Only Grand Total in Power BI Matrix

Cody Schneider•

Trying to show just the grand total in a Power BI matrix can feel surprisingly complicated for what seems like a simple goal. You don't want the rows of detailed data, you just need that one important bottom-line number. This guide will walk you through a few effective methods, from a quick visual trick to a more powerful DAX formula, to get your report looking exactly the way you want.

Why Would You Want to Show ONLY the Grand Total?

Hiding the detailed rows in a matrix isn’t just for looks, it serves several practical purposes in reporting. You might want to isolate the grand total to focus your audience's attention on the most important takeaway number.

Here are a few common scenarios where this technique is a lifesaver:

  • Creating Big-Picture KPI Cards: One of the most common use cases is to create a Key Performance Indicator (KPI) card. When the CEO or a department head glances at a dashboard, they often want to see the main numbers first - Total Revenue, Total Users, Total Leads - without getting bogged down in the breakdown by salesperson, region, or marketing campaign. A matrix with only the grand total acts as a dynamic, large-format KPI.

  • Building Summary Dashboards: For high-level summary reports, the detail is noise. By stripping away individual rows, you create a cleaner, more readable view that highlights final calculations. It helps you build a summary view that can sit alongside other charts that provide a more granular view, without duplicating the details.

  • Reducing Visual Clutter: A matrix with hundreds or thousands of rows can be overwhelming. Showing only the total simplifies the report, making it less intimidating and easier to interpret instantly. This is crucial when presenting data in meetings where time is short and clarity is everything.

  • Using Matrix Structure for Columnar Totals: Sometimes you need the structure of a matrix - specifically its ability to show totals across column hierarchies (like Year or Quarter) - but you don't care about the row detail. In this case, a standard Card visual won't work, so isolating the grand total in a matrix is the perfect solution.

The Quick & Easy Method: The Formatting Trick

If you need a fast solution and aren’t concerned with the underlying data structure, a clever formatting trick can get you 90% of the way there. This method works by essentially making the row headers invisible.

This approach is perfect for a quick visual fix, but keep in mind that the data for the rows is still technically present in the visual - it’s just hidden from sight.

Step-by-Step Instructions

Let's use an example where we have a matrix showing Total Sales by Product Category and Region.

1. Disable the Stepped Layout

This is the most critical first step. By default, Power BI matrices use a "stepped" layout where multiple row headers are indented under a single column. We need to disable this to manipulate each field's column individually.

  • Select your Matrix visual.

  • Go to the Format your visual pane (the paintbrush icon).

  • Expand the Row headers section.

  • Find the Stepped layout toggle and switch it OFF.

You’ll immediately see your row fields (Product Category, Region) separate into their own columns.

2. Shrink the Row Header Columns

Now that your row headers are in separate columns, you can resize them - down to nothing.

  • Hover your mouse over the right edge of the column header for "Product Category."

  • Your cursor will change to a double-arrow drag icon.

  • Click and drag the edge all the way to the left until the column completely disappears.

  • Repeat this process for any other row header columns you have, like "Region."

After shrinking the columns, all your detailed rows will be hidden from view, leaving only the "Grand Total" row visible at the bottom.

3. Final Touches (Optional)

To really clean it up, you can turn off the automatic sub-totals, which might still appear if you have multiple row fields.

  • In the Format your visual pane, go to the Subtotals section.

  • There are toggles for Row subtotals you can turn off if desired for an even cleaner look depending on your exact configuration.

With this simple trick, your matrix now appears to show only the grand total - a perfect solution for a visual KPI card.

The More Robust Method: Using a DAX Measure

For a more solid, data-driven solution, you can use DAX (Data Analysis Expressions), Power BI’s formula language. This method prevents the values for the detailed rows from being calculated in the first place, replacing them with blanks.

The core of this method relies on a DAX function called HASONEVALUE, which checks if a field is being filtered down to a single value. In the context of a matrix, this function effectively helps us tell the difference between a detail row (which represents a single value, like "Electronics") and a total row (which represents multiple values).

The DAX Formula Logic

The logic we’ll build for our new DAX measure is simple:

“IF we are looking at a single row context (a specific product category), then show nothing (BLANK). OTHERWISE (if we’re at the total level), show the actual sales calculation.”

Step-by-Step Instructions

First, make sure you have a base measure to calculate your value. For example:

Total Sales = SUM(Sales[Revenue])

Now, let’s create the new measure that will only calculate the grand total.

1. Create a New Measure

  • Go to the Home or Modeling tab in the Power BI ribbon.

  • Click on New measure.

2. Enter the DAX Formula

In the formula bar that appears, type the following DAX expression. Make sure to replace 'SalesTable'[Product Category] with the actual table and column name for the field you have on your matrix rows.

Grand Total Sales Only = IF( HASONEVALUE('SalesTable'[Product Category]), BLANK(), [Total Sales] )

3. Use the New Measure in Your Matrix

  • Deselect your original [Total Sales] measure from the Values field of your matrix visual.

  • Find your new measure, Grand Total Sales Only, in the Fields pane and drag it into the Values field.

You will now see a matrix where all the detail rows are blank, but the Grand Total row shows the correct value. The row headers are still there, but the numbers next to them are gone.

Handling Multiple Row Levels

What if you have more than one field in your rows (e.g., Category and Region)? You’ll need to adjust your DAX to account for both.

You can use the OR logic (the || symbol in DAX) to check if any of the detail rows have a single value.

Grand Total Only (Multi-Level) = IF( HASONEVALUE('SalesTable'[Product Category]) || HASONEVALUE('SalesTable'[Region]), BLANK(), [Total Sales] )

This formula says: "If we are looking at a single category OR a single region, show a blank. Otherwise, show the total." This ensures every level of the hierarchy except the final grand total is blank.

Combining Both Methods for a Perfect Result

The best and cleanest solution is to use the DAX method and then finish with the formatting trick.

  • Create and apply your "Grand Total Only" DAX measure to the matrix visual.

  • Follow the steps from the "Formatting Trick" to turn OFF the stepped layout and shrink the row header columns until they disappear.

The result is a powerful visual that looks like a clean KPI card but is built on a solid DAX foundation. No extra data is being displayed or hidden - it's simply not being calculated, which is a much more efficient approach.

What About the Simple Card Visual?

It's worth mentioning that if your only goal is to show a single, unfiltered grand total, Power BI's built-in Card visual is often the simplest choice.

You can simply drag your base measure (like [Total Sales]) onto a Card visual, and it will display the total with no fuss.

So, why go through the trouble with the matrix at all? The matrix method we've outlined becomes essential when you need a structure that a Card can't provide - specifically, when you have fields in the Columns area of your matrix. For example, if you want to see the grand total of sales broken down by Year across the columns, a Card can't do that. A matrix is the only way to achieve that layout, making these techniques indispensable.

Final Thoughts

Stripping a Power BI matrix down to only its grand total is a common reporting need, and knowing how to do it opens up a new level of dashboard design flexibility. The quick method of hiding columns is great for a fast visual adjustment, while the DAX HASONEVALUE approach gives you a more robust and efficient solution for creating truly clean summary views.

We know that getting your reports just right in powerful tools like Power BI can sometimes involve searching for clever formulas and hidden settings. What if you could skip the workarounds entirely? With Graphed, we’ve created an easier path to insights. Simply connect your data sources - like Google Analytics, Shopify, and Salesforce - and ask for what you need in plain English. You can say "Show me a KPI for total sales last month" and instantly get a live-updating visualization without building DAX measures or adjusting formatting panes. It allows you to chat with your data to get the answers you need in seconds instead of a lengthy build process.