Why Your Power BI Total Is Wrong: The DAX Mistake Everyone Makes (and the Fix)

The Scenario: A Sales Performance Report with Broken Totals

You’re building a Power BI report for a sales team:

• Fact table: Sales (one row per order line)
• Dimensions: Customers, Products, Calendar
• Visual: a matrix with Customer[Customer Name] on rows and a custom measure for margin % in values

The team wants a margin percentage that:

1. Excludes internal test orders
2. Excludes free samples
3. Shows correct margin % per customer

You write this measure:

Margin % (wrong) = 
VAR NetSales = 
    CALCULATE(
        SUM ( Sales[NetAmount] ),
        Sales[IsTestOrder] = FALSE,
        Sales[IsSample] = FALSE
    )
VAR NetCost = 
    CALCULATE(
        SUM ( Sales[CostAmount] ),
        Sales[IsTestOrder] = FALSE,
        Sales[IsSample] = FALSE
    )
RETURN
    DIVIDE ( NetSales - NetCost, NetSales )

Row‑level values look right. But the total margin % at the bottom of the matrix doesn’t match the finance team’s number from their SQL query, even though they’re using the same filters.

The numbers in each row are correct; the grand total is wrong.

Why Totals Are "Wrong": DAX Measures Are Re‑Evaluated, Not Summed

The key point: a measure is not aggregated from its row values. Power BI does not add up the visible numbers to get the total. It re‑evaluates the measure once in the total row’s filter context.

In the matrix:

• Each customer row has a filter context: Customer[Customer Name] = X, plus any slicers/page filters.
• The grand total row has no customer filter; it only has slicers/page filters.

So Margin % (wrong) is evaluated:

• Per customer: on that customer’s rows in Sales.
• In the total: on all rows in Sales (subject to slicers), with no customer filter.

That behaviour is correct DAX. The problem is the measure design.

The Classic Mistake: Ratio of Row‑Level Ratios

In our scenario, the matrix is showing:

• Row: Margin % (wrong) evaluated per customer
• Total: Margin % (wrong) evaluated over all customers

If the team expects the total to be:

Sum of NetMargin / Sum of NetSales (over all customers)

…then the measure is fine; DAX already does that.

But what people often expect (without realising) is:

Weighted average of the visible customer margin % values

Those two are only the same if each customer has the same NetSales. In real data, they differ. The visual shows the correct ratio over the total filter context, but the team is mentally aggregating the row‑level ratios.

This is the core of "why your Power BI total is wrong":

• Totals are correct for the measure’s logic.
• Totals are wrong for the business expectation when the measure mixes row‑level logic with aggregation incorrectly.

The Real DAX Pitfall: Row Context vs Filter Context in Totals

The more subtle mistake appears when you use iterators (SUMX, AVERAGEX, etc.) with row context and then expect totals to behave like simple sums.

Consider a more complex version of the same measure, where you try to pre‑calculate margin per order:

Margin % per Order (wrong) = 
VAR Orders = 
    FILTER ( 
        Sales, 
        Sales[IsTestOrder] = FALSE 
            && Sales[IsSample] = FALSE 
    )
VAR MarginPerOrder = 
    ADDCOLUMNS ( 
        Orders,
        "OrderMargin", Sales[NetAmount] - Sales[CostAmount],
        "OrderMarginPct", 
            DIVIDE ( Sales[NetAmount] - Sales[CostAmount], Sales[NetAmount] )
    )
RETURN
    AVERAGEX ( MarginPerOrder, [OrderMarginPct] )

This measure:

• Creates a table of order lines (filtered to real orders)
• Adds a margin % per order line
• Returns the average margin % per order line in the current filter context

At the customer level, this might match what the team expects. At the total level, it becomes the average of all order‑line margins across all customers. If finance expects the total margin % to be based on total margin / total sales, this total is wrong.

The mistake:

• Designing a measure whose semantics change between row and total contexts
• Using iterators that aggregate row‑level ratios without regard to business logic

The Fix: Separate Business Logic from Aggregation

The most reliable pattern to avoid broken totals is:

1. Create base measures for additive quantities (Sales, Cost, Margin).
2. Define ratio measures (margin %, average price, etc.) only in terms of base measures, not row‑level calculations.

Step 1: Base Measures

Net Sales = 
CALCULATE ( 
    SUM ( Sales[NetAmount] ),
    Sales[IsTestOrder] = FALSE,
    Sales[IsSample] = FALSE
)

Net Cost = 
CALCULATE ( 
    SUM ( Sales[CostAmount] ),
    Sales[IsTestOrder] = FALSE,
    Sales[IsSample] = FALSE
)

Net Margin = 
[Net Sales] - [Net Cost]

These measures:

• Respect the current filter context (customer, date, product, etc.).
• Apply the same exclusion logic everywhere (rows and totals).

Step 2: Ratio Measure Using Base Measures

Margin % = 
DIVIDE ( [Net Margin], [Net Sales] )

Now:

• Per customer row, [Margin %] is customer margin / customer sales.
• In the grand total row, [Margin %] is total margin / total sales.

The total is not the average of customer margin %, it is the ratio over the total filter context – which is what finance usually expects.

If the business really wants the average of customer margins (each customer weighted equally), you can define that explicitly:

Margin % (avg customers) = 
VAR CustomerTable = 
    VALUES ( Customer[Customer ID] )
VAR CustomerMarginTable = 
    ADDCOLUMNS ( 
        CustomerTable,
        "CustomerMarginPct", 
            VAR CustomerNetSales = CALCULATE ( [Net Sales] )
            VAR CustomerNetMargin = CALCULATE ( [Net Margin] )
            RETURN DIVIDE ( CustomerNetMargin, CustomerNetSales )
    )
RETURN
    AVERAGEX ( CustomerMarginTable, [CustomerMarginPct] )

This measure:

• Treats each customer equally in the total, regardless of sales volume.
• Will give a different total from Margin % when customer sizes vary.

The important part is that the measure’s definition matches the business expectation.

Other Common Total Issues from DAX Design

Once you understand that totals are re‑evaluated, not summed, several other "wrong total" patterns make sense.

1. Measures That Ignore Visual Filters

Using ALL or REMOVEFILTERS without care can make totals look detached from the matrix.

Example:

Sales % of All Customers = 
DIVIDE ( 
    [Net Sales],
    CALCULATE ( [Net Sales], ALL ( Customer ) )
)

Behaviour:

• Row: customer sales / total sales over all customers, regardless of row filters.
• Total: total sales / total sales over all customers → 1 (100%).

If someone expects the total to show "sum of the row percentages", they will call this a wrong total. But the measure is doing exactly what it says.

Fix is not technical; it’s about naming and expectation:

• Use a precise name: Sales % of All Customers.
• Explain that the total is always 100%.

If the requirement is "percentage of visible customers", then change the denominator:

Sales % of Visible Customers = 
VAR VisibleCustomerSales = [Net Sales]
VAR VisibleTotalSales = 
    CALCULATE ( [Net Sales], ALLSELECTED ( Customer ) )
RETURN
    DIVIDE ( VisibleCustomerSales, VisibleTotalSales )

ALLSELECTED respects slicers and outer visuals, so the total matches the sum of row percentages.

2. Measures with Implicit Filters Inside Iterators

Another pattern:

High Margin Orders Count (wrong) = 
COUNTROWS ( 
    FILTER ( 
        Sales,
        Sales[IsTestOrder] = FALSE
            && Sales[Margin] > 0.3
    )
)

This measure counts orders with margin > 30% ignoring any existing filter on Sales[Margin] coming from the visual. It also ignores any dimension filters that don’t propagate, depending on relationships.

Per customer, this might look reasonable. In the total, it often surprises people because it counts orders they don’t think are "in the report".

A safer pattern is to apply conditions on top of the current filter context rather than replacing it completely:

High Margin Orders Count = 
CALCULATE ( 
    COUNTROWS ( Sales ),
    Sales[IsTestOrder] = FALSE,
    Sales[Margin] > 0.3
)

Here, existing filters (customer, date, product) remain in effect for both rows and totals.

Practical Design Pattern: Build a "Totals‑Safe" Margin Model

Back to the sales team scenario. To make the report robust and shareable, you want a pattern that:

• Keeps business logic in one place
• Works identically in rows, subtotals, and grand totals
• Survives new visuals and slicers without surprises

1. Use a Dedicated Measure Table

Create a measure table (e.g. Measures) and put all measures there. This doesn’t change behaviour but makes it easier to:

• See all base and derived measures
• Control dependencies between measures

2. Define a Clear Layering

• Base measures: [Net Sales], [Net Cost], [Net Margin]
• Ratio measures: [Margin %], [Sales % of All Customers], etc.
• Advanced aggregations: [Margin % (avg customers)], scenario‑specific logic

3. Test Totals in a Dedicated Matrix

Build a simple matrix for validation:

• Rows: Customer[Customer Name]
• Values: [Net Sales], [Net Margin], [Margin %], [Margin % (avg customers)]

Check:

• Does the total of [Net Sales] match the source system?
• Does [Margin %] at the total match TotalMargin / TotalSales from SQL or Excel?
• Does [Margin % (avg customers)] match the average of visible row margin % values?

This matrix becomes your "DAX contract" with stakeholders. Once they agree on which measure corresponds to which business question, disputes about totals drop sharply.

Downloadable Example: What to Put in the File

To make this concrete for your team, build and share a .pbix with:

1. A small Sales table (import mode) with columns:
   • OrderID (text or integer)
   • CustomerID
   • NetAmount
   • CostAmount
   • IsTestOrder (TRUE/FALSE)
   • IsSample (TRUE/FALSE)
2. A Customer dimension with CustomerID, Customer Name.
3. Relationships: Sales[CustomerID] → Customer[CustomerID] (single, one‑to‑many).
4. Measures:
   • Net Sales
   • Net Cost
   • Net Margin
   • Margin %
   • Margin % (avg customers)
   • Sales % of All Customers
   • Sales % of Visible Customers
5. Two matrices:
   • Validation matrix (all measures side by side)
   • Business matrix (only the measures the team will actually use)

This file becomes your reference implementation of "totals‑safe" measures. When someone reports a new "wrong total" in another report, you can compare their measure pattern against this file and usually spot the issue quickly.

One Concrete Takeaway

Whenever a total looks wrong, don’t start by checking the visual – start by reading the measure. If you can’t explain what the measure is supposed to mean at the grand‑total filter context, redesign it using base measures and explicit aggregation until the total is a direct, obvious consequence of the business logic.