How to Add Totals in Power BI

Showing totals in your reports seems like it should be the easiest part, but Power BI’s powerful calculation engine can sometimes produce grand totals that don't look like a simple sum of the rows above. This article walks you through adding and customizing totals in your tables and matrices and then shows you how to write a bit of DAX to take full control and fix totals that aren't behaving as you expect.

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

Adding Totals to Tables and Matrices: The Easy Way

For standard tables and matrices, adding totals is a simple toggle in the formatting options. Let’s quickly cover how it’s done, as the controls differ slightly between the two types of visuals.

For a Table Visual

The Table visual in Power BI displays data in a straightforward grid of rows and columns. Adding a total row that sums up your numeric columns takes just a few clicks.

1. Select the table visual on your report canvas.
2. Go to the Format visual pane (the paintbrush icon).
3. Expand the Totals section.
4. Switch the Totals toggle to On.

Just like that, a “Total” row appears at the bottom of your table, providing a sum for each numeric field in the visual. You can also customize the label, text formatting, and background color right from this same menu.

For a Matrix Visual

The Matrix visual is more like a pivot table, allowing you to have rows, columns, and value hierarchies. Because of this added complexity, you have more control over where and how totals appear.

In a matrix, these summaries are often referred to as subtotals (for inner levels of a hierarchy) and grand totals (for the final totals for all rows and columns).

To add row and column grand totals:

1. Select the matrix visual on your canvas.
2. Go to the Format visual pane.
3. Expand the Row subtotals section and toggle it On if you want totals for each level of your row hierarchy. You can also choose whether to show them at the top or bottom of each group.
4. Expand the Column subtotals section and do the same for your column hierarchies.
5. For the overall grand totals, expand the Row grand total and Column grand total sections and toggle them On.

The matrix gives you far more granular control, which is essential when you're working with nested data. You might only want a grand total at the end, without subtotals for every single category, and the formatting pane makes this easy to configure.

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

Customizing the Appearance of Your Totals

Once you've turned your totals on, you’ll probably want to make them stand out. Power BI gives you plenty of simple options to format the total rows so they are clearly distinguished from the rest of your data.

Within the same Totals (for tables) or Grand total/Subtotals (for matrices) sections of the Format pane, you'll find formatting options like:

• Label: You can change the default text from "Total" to anything you’d like, such as "Grand Total," "Company-Wide Sales," or "Overall Performance."
• Values: This lets you customize the text formatting. You can make the total values bold, change the font color or size, and apply a different background color to make the entire row pop.
• Apply to header: In a Matrix, you can choose if the formatting should apply just to the values or to the headers in the totals section as well.

Taking a minute to style your totals improves report readability significantly. A user can find the bottom line they're looking for much faster if the total row has distinct formatting.

Fixing “Incorrect” Grand Totals with DAX

Sooner or later, every Power BI user runs into this scenario: you've built a visual, the individual rows of data look perfect, but the grand total is just... wrong. It's not a bug, it's a feature of how Power BI's calculation engine works.

Understanding Filter Context

The key to understanding strange totals is filter context. For each row in your table, Power BI calculates your measure in that row's specific context. If a row is for "Product A," the measure is calculated with a filter applied for only Product A's data.

The Total row, however, has a different filter context. It is not calculated by simply summing up the visible numbers in the rows above it. Instead, Power BI removes the row-level filters (like "Product A") and re-calculates the original DAX measure over the entire unfiltered dataset for that visual.

For a simple measure like Total Sales = SUM(Sales[Revenue]), this works perfectly. The sum of all unfiltered sales is the same as adding up each product’s sales.

But for more complex measures, like averages, ratios, or Top N calculations, this can produce a result you didn't expect.

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: Summing the Sales of Top 5 Customers Per Region

Imagine your goal is to show a matrix with Regions as rows. For each Region row, you want to see the total sales generated by the Top 5 customers in that region. Then, in the Grand Total row, you want to see the SUM of those Top 5 values from each region combined.

A simple DAX measure might look something like this:

-- An initial attempt that will produce the "wrong" Grand Total
Sales of T5 Customers [Wrong Total] =
CALCULATE(
    SUM(Sales[SalesAmount]),
    TOPN(5, VALUES(Customers[Customer Name]), SUM(Sales[SalesAmount]))
)

In a row for the "East" region, this works perfectly. It calculates the sales for the top 5 customers in the East.

But in the Grand Total row, the filter context is all regions. So this formula finds the top 5 customers for the entire company and shows their sales number. That’s a completely different value from the sum of each region’s top 5 sales figures. This is where we need smarter DAX.

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

The Solution: Using IF and HASONEVALUE to Control Total Logic

We can solve this by creating a measure that checks its own filter context. We will tell it: “If you are being calculated for a single region, do the Top 5 logic. If you are being calculated for the Grand Total row, do something different.”

The HASONEVALUE() function is perfect for this. It returns TRUE if only a single value for the specified column is being filtered. In our matrix, HASONEVALUE('Geography'[Region]) will be TRUE for the East, West, North, and South rows, but FALSE for the Grand Total row.

Let's build the correct measure using this logic.

Sales of T5 Customers [Correct Total] =
VAR SalesT5forRegion =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        TOPN(
            5,
            VALUES(Customers[Customer Name]),
            CALCULATE(SUM(Sales[SalesAmount]))
        )
    )
RETURN
    IF(
        HASONEVALUE(Geography[Region]),
        SalesT5forRegion,  -- Logic for the individual rows
        SUMX(
            VALUES(Geography[Region]),
            SalesT5forRegion -- Logic for the Grand Total
        )
    )

Breaking Down the DAX Formula:

1. The Variable (VAR): We first define a variable, SalesT5forRegion, to hold our core calculation. This is the same logic as our "wrong" measure from before - it finds the total sales for the top 5 customers in the current filter context. Using variables keeps the code cleaner and more efficient.
2. The IF Check: Next, the IF() statement uses HASONEVALUE(Geography[Region]) to see what kind of row it’s on.
3. If it’s a single region (TRUE): If HASONEVALUE is true, the formula simply returns our SalesT5forRegion variable. This part handles the state-by-state rows and gives the correct T5 sales number.
4. If it’s the Grand Total (FALSE): This is the clever part. In the else portion of our IF(), we use SUMX(). SUMX is an "iterator" function - it goes through a table you give it, row-by-row, performs a calculation, and then sums the results.

By creating a measure that behaves differently depending on its context, you gain complete control over your grand totals. This IF(HASONEVALUE(), [RowLogic], [TotalLogic]) pattern is one of the most powerful tools for solving complex totals problems in Power BI.

Final Thoughts

Adding totals in Power BI can be as simple as flipping a switch in the formatting pane, or it can be a gateway to understanding the fundamentals of DAX. While the 'on/off' toggle works for basic sums, taking the time to learn patterns like SUMX and HASONEVALUE will give you the ability to build accurate and sophisticated reports that meet any stakeholder requirement.

It's workflows like this that can make data analysis feel complex, requiring time to learn specific functions and syntax. At Graphed, we handle this complexity for you. By connecting your data sources and asking questions in plain English - like "Show me a chart of total sales by region" - our AI generates the charts and reports you need in seconds, without you ever having to write a line of code or figure out the intricacies of filter context.