Graphed LogoGraphed
Graphed LogoGraphed
FeaturesUse CasesIntegrationsPricing

How to Add Grand Total in Power BI Table

Cody Schneider••8 min read

Seeing a grand total in your Power BI table should be straightforward, but sometimes it requires a bit of manual setup or a clever DAX formula to get it just right. Whether your total row is missing, showing the wrong number, or you just want to customize its appearance, you've come to the right place. This guide walks you through the simple toggle switches and the essential DAX patterns for mastering grand totals in Power BI tables and matrices.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

The Easiest Way: Using the Format Pane

For most simple tables, adding a total is just a couple of clicks away. Power BI has a built-in feature designed for exactly this purpose. Let's use a common scenario: a table showing total sales broken down by product category.

Here’s the standard step-by-step process:

  • Select your visual: Click on the table or matrix in your Power BI report canvas to activate it.
  • Go to the Visualizations pane: On the right-hand side of your screen, you'll see the Visualizations pane. Click on the icon that looks like a paintbrush, which is the "Format your visual" tab.
  • Find the "Totals" section: Scroll down through the list of formatting options. You'll find a card labeled "Totals." Expand it by clicking the arrow next to its name.
  • Turn it on: You'll see a simple on/off toggle for "Values." Switch this to the "On" position. A "Total" row should immediately appear at the bottom of your table, summing up the numeric columns you’ve added.

That's it! In many cases, this is all you need to do. Power BI will automatically sum up the values and display the result at the bottom.

Customizing Your Grand Total

Once you've enabled the total row, you can customize it to make it stand out. Within that same "Totals" section in the Format pane, you have a few options:

  • Label: You can change the default text from "Total" to something more descriptive like "Grand Total," "Company-Wide Total," or whatever fits your report. Just type your custom title in the "Text" field under the "Label" option.
  • Formatting: You can change the font, text size, color, and background color for both the label and the value in the total row. This is incredibly useful for directing your audience's attention to the final numbers. Making the total row bold or giving it a subtle background color is a common best practice.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Why Is My Total Incorrect? Understanding Filter Context

Sometimes you enable the grand total, and the number just looks... wrong. It’s not adding up the rows above it. This is one of the most common points of confusion for new Power BI users, and it almost always comes down to something called filter context.

Here's the key concept: The total row is not a sum of the rows you see in the table. Instead, Power BI recalculates your DAX measure for the total row with the filters removed.

A classic example is a measure that calculates a distinct count, like "Distinct Customer Count."

Imagine your table looks like this:

  • East Region: 500 distinct customers
  • West Region: 700 distinct customers
  • Total: 1,000 distinct customers

You might look at that and think, "Wait, 500 + 700 is 1,200, not 1,000!" The total is "correct" from Power BI's perspective. There are probably 200 customers who bought products from both the East and West regions. When the measure calculates for the total row, it removes the "Region" filter and calculates the distinct count of customers across the entire dataset, which is 1,000.

Fixing Totals with SUMX

If you genuinely need your total row to be a sum of the values above it, you need to use an "iterator" function in DAX, like SUMX.

Functions like SUMX, AVERAGEX, and COUNTX allow you to perform a calculation for each row of a given table and then aggregate the results. To fix our distinct count example, you would create a new measure.

Let's assume your original, 'incorrect' measure was:

Distinct Customers = DISTINCTCOUNT('Sales'[CustomerID])

To get a total that adds up the row values, your DAX would look like this:

Sum of Distinct Customers by Region = 
SUMX(
    VALUES('Geography'[Region]),
    [Distinct Customers]
)

Let's break that down:

  • SUMX(...): This tells Power BI to iterate over a table and sum up the results of an expression.
  • VALUES('Geography'[Region]): This provides the table for SUMX to iterate over. It returns a one-column table containing the unique values of the 'Region' column that are currently visible.
  • [Distinct Customers]: This is the expression that SUMX will calculate for each region.

Essentially, this formula tells Power BI: "Go through each Region, calculate the distinct customer count for it, and then add up all those individual results." The result is a total that perfectly reflects the sum of the rows above it.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Creating Conditional Totals in DAX

What if you want the total row to calculate something completely different from the rows above it? For example, you might want to show the 'Average Sale Value' for each product category, but for the total row, you want it to show the 'Total Sales Value' for all categories combined.

This is where helpful DAX functions like HASONEVALUE or ISINSCOPE come into play. These functions can detect whether the formula is being calculated for a single category row or the overarching total row.

You can use an IF statement to change the behavior of the measure accordingly.

Here’s the pattern:

Measure logic = 
IF(
    HASONEVALUE('Dimension'[Column]),
    [Calculation for individual rows],
    [Calculation for total row]
)

Let's apply this to our "Average vs. Total Sales" example:

Sales Avg or Total = 
IF(
    HASONEVALUE('Product'[Category]),
    AVERAGE('Sales'[Sales Amount]),  // Value if true (for each category)
    SUM('Sales'[Sales Amount])      // Value if false (for the total row)
)

When this measure is evaluated for a single category row (e.g., "Electronics"), HASONEVALUE('Product'[Category]) is true, and the formula returns the average sales amount. When it's evaluated for the total row, which considers all categories, HASONEVALUE is false, and the formula switches to returning the total sum of sales. This gives you complete control over your grand totals.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Totals in a Matrix Visual

A matrix visual is like a pivot table - it has both rows and columns. This means it has more options for totals and subtotals. The process starts the same way: select the matrix and go to the "Format your visual" tab in the Visualizations pane.

But instead of a single "Totals" section, you'll see separate settings:

  • Row grand total: This adds a total column on the right side of your matrix, summarizing each row's values.
  • Column grand total: This adds a total row at the bottom of your matrix, summarizing each column's values.

You can enable one, both, or neither, depending on your needs. Each can be formatted independently with labels, colors, and fonts.

Additionally, you'll find a "Subtotals" section. If you have a hierarchy on your rows or columns (e.g., Category and Sub-Category), you can use this section to turn subtotals on or off for each level of the hierarchy, giving you granular control over where summarization appears in your visual.

Troubleshooting Common Grand Total Issues

If you're still running into problems, here's a quick checklist of common issues and their solutions.

  • The 'Totals' option is greyed out. This may happen if the value you are trying to total does not support aggregation. Make sure you are using a numeric data type. It can also happen with certain DAX measures that are written in a way that prevents a logical sum. Review your formulas and ensure they use standard aggregation functions like SUM, AVERAGE, or COUNT.
  • My total doesn’t appear for a text column. Totals only work on aggregatable data, which means numbers. You cannot get a "total" of product names or text descriptions.
  • The total is slowing down my report significantly. If you’re using an iterator like SUMX over a very large table with millions of rows, performance can suffer. This is because the function has to perform many calculations. To fix this, try to create a more efficient version of the base measure being iterated over, or see if you can pre-aggregate data in Power Query or your data source before it gets to the DAX engine.

Final Thoughts

Adding a grand total in Power BI can be a one-click affair in the format pane, or it can be a deeper dive into the logic of DAX. Understanding filter context is the key to unraveling why your totals might be different than expected, while functions like SUMX and HASONEVALUE give you the scripting power to make your grand total row behave exactly how you need it to.

Learning the intricacies of BI tools like Power BI can be incredibly powerful, but it often involves a steep learning curve with DAX, data modeling, and endlessly clicking through formatting panes. We built Graphed because we believe getting insights shouldn't require so much manual work. Instead of building tables and writing measures, you can simply connect your data sources and ask questions in natural language like, "Show me my total sales by product category in a table" to get a live, real-time dashboard built for you in seconds.

Related Articles