Wrong Total in Power BI? Why the Total Row Doesn’t Add Up (and How to Fix It)

Scenario: Sales Performance Report with a “Wrong” Total

Imagine a common setup:

• Fact table: Sales (columns: Date, CustomerID, ProductID, Quantity, NetAmount, DiscountAmount, TargetAmount)
• Dimension tables: Customers, Products, Calendar
• Report page: Matrix by Customer and Year, with measures for Net Sales, Target, and Achievement %

You define:

Net Sales = SUM ( Sales[NetAmount] )

Target = SUM ( Sales[TargetAmount] )

Achievement % = DIVIDE ( [Net Sales], [Target] )

At the customer-year level, Achievement % looks reasonable. But the total row shows 104% while you know overall achievement should be closer to 97%. When you export to Excel and sum the visible rows, you get 103.8%, not 104%, and neither matches the “true” overall ratio.

This is the classic “Wrong Total in Power BI” problem: the total row isn’t a simple sum of the visible rows, and in many cases it shouldn’t be.

Core Concept: Totals Are Separate Filter Contexts, Not Sums of Cells

The key point: Power BI does not sum up the visible cells to compute a total row.

For a matrix or table visual:

• Each detail row is evaluated with its own filter context (e.g. a specific customer and year).
• The total row is evaluated with a different filter context (e.g. all customers for that year, or all customers and all years, depending on the subtotal/grand total).

DAX measures are always evaluated as:

Measure = expression evaluated under the current filter context

So if the measure is non-additive (like a ratio, average, or distinct count), the total row is not supposed to be the sum of the row values.

In the scenario:

• Row-level Achievement % = Net Sales for that customer / Target for that customer.
• Total-level Achievement % = total Net Sales / total Target across all customers.

Those two are mathematically different operations.

This explains the symptom:

• The total row is correct for its context, but doesn’t match the sum of visible row values.

The fix is not “make Power BI sum the rows”, but “make the measure explicit about which total behaviour you want”.

Cause #1: Ratios and Percentages Are Non-Additive

The most common cause of “wrong totals” is ratios:

• Margin %
• Conversion rate
• Achievement %
• Utilisation %

These are non-additive by definition.

Example: Achievement % Done the Naive Way

In our scenario:

Achievement % = DIVIDE ( [Net Sales], [Target] )

This measure:

• Works row-by-row (each customer-year).
• At the total row, evaluates DIVIDE( SUM(Sales[NetAmount]), SUM(Sales[TargetAmount]) ) under the total filter context.

If someone expects the total to be average of customers’ Achievement % or sum of visible percentages, they’ll call it “wrong”. But mathematically, the DAX behaviour is correct for a ratio.

Fix Pattern: Separate Row-Level and Total-Level Logic

You can control total behaviour by explicitly defining which aggregation you want.

Pattern A: Weighted Ratio (Preferred for Business Accuracy)

If the business wants overall achievement as total Net Sales / total Target, the naive measure is already correct. The “wrong total” perception comes from comparing it to a simple average of visible row percentages.

In that case:

• Keep Achievement % as is.
• Educate stakeholders: the total is a weighted ratio, not an average of percentages.

Pattern B: Average of Visible Row Percentages

If the requirement is to show the total as the average of the visible row percentages, you must implement that explicitly.

One reliable pattern is to compute the row-level ratio for each entity (e.g. customer) and then average those ratios at the total level.

Example where the matrix rows are Customer[CustomerName]:

Achievement % (Avg of Customers) = 
VAR CustomersVisible = VALUES ( Customer[CustomerID] )
RETURN
    AVERAGEX ( 
        CustomersVisible,
        VAR NetSalesCustomer = CALCULATE ( [Net Sales] )
        VAR TargetCustomer   = CALCULATE ( [Target] )
        RETURN 
            DIVIDE ( NetSalesCustomer, TargetCustomer )
    )

Behaviour:

• Row-level (per customer): VALUES(Customer[CustomerID]) returns a single value, so AVERAGEX averages one element – effectively the same as DIVIDE(NetSalesCustomer, TargetCustomer).
• Total row: CustomersVisible is the set of customers in the current filter context; AVERAGEX returns the average of each customer’s ratio.

This now matches the “average of visible row percentages” expectation.

Important Note: Filter Context Comes from the Visual

The logic above relies on VALUES(Customer[CustomerID]) matching the row granularity of the visual.

If the matrix rows are at another grain (e.g. Customer[Segment] instead of individual customers), adjust the measure to match that grain:

Achievement % (Avg of Segments) = 
VAR SegmentsVisible = VALUES ( Customer[Segment] )
RETURN
    AVERAGEX ( 
        SegmentsVisible,
        VAR NetSalesSegment = CALCULATE ( [Net Sales] )
        VAR TargetSegment   = CALCULATE ( [Target] )
        RETURN 
            DIVIDE ( NetSalesSegment, TargetSegment )
    )

The pattern is the same; only the grouping column changes.

Cause #2: Measures Using Row Context (Iterators, EARLIER, Calculated Columns)

Another frequent source of confusion is when a measure depends on row context (e.g. SUMX, AVERAGEX, or logic originally written for calculated columns).

Scenario Variant: Sales per Active Customer

You add a measure:

Sales per Active Customer = 
VAR ActiveCustomers = DISTINCTCOUNT ( Customer[CustomerID] )
RETURN
    DIVIDE ( [Net Sales], ActiveCustomers )

At the customer row level, ActiveCustomers = 1, so the measure just returns [Net Sales]. At a segment total, ActiveCustomers is the count of customers in that segment, so the total row shows segment sales / number of customers.

If someone expects the total row to be the sum of row-level Sales per Active Customer, they’ll see a mismatch.

Fix Pattern: Define Explicit Grain for the Metric

For metrics like “sales per active customer”, decide what grain you care about:

• Per customer: then your visual should show customers, and the total is not meaningful as a sum of per-customer values.
• Per segment: then define a segment-level measure and use it consistently.

Example: segment-level metric, matching total behaviour:

Sales per Active Customer (Segment) = 
VAR SegmentCustomers = DISTINCTCOUNT ( Customer[CustomerID] )
RETURN
    DIVIDE ( [Net Sales], SegmentCustomers )

Use this on a visual where rows are segments. Now:

• Segment row = segment sales / customers in that segment.
• Grand total = overall sales / overall customers, which is consistent with the definition.

If you truly need a total that sums the visible row values, you must materialise the row-level metric and then aggregate it.

Example: sum of visible row values in a matrix where rows are customers:

Sales per Active Customer (Sum of Rows) = 
VAR CustomersVisible = VALUES ( Customer[CustomerID] )
RETURN
    SUMX ( 
        CustomersVisible,
        VAR NetSalesCustomer = CALCULATE ( [Net Sales] )
        VAR ActiveCustomersCustomer = 1
        RETURN 
            DIVIDE ( NetSalesCustomer, ActiveCustomersCustomer )
    )

Here the total row is literally the sum of row-level values.

Cause #3: Distinct Counts and Semi-Additive Measures

Distinct counts and semi-additive measures (like stock levels, last non-blank) are also non-additive.

Distinct Count Example

Measure:

Active Customers = DISTINCTCOUNT ( Sales[CustomerID] )

In a matrix by Product[Category]:

• Row: active customers for that category.
• Total row: distinct customers across all categories, not the sum of row-level values.

If a customer buys from multiple categories, they are counted once in the total.

Fix Pattern: Explain or Redefine the Metric

You have two valid interpretations:

1. Unique customers across all categories (current behaviour).
2. Sum of category-level customer counts (double-counting customers across categories).

If the business needs “sum of category-level counts”, implement it explicitly:

Active Customers (Sum of Categories) = 
VAR CategoriesVisible = VALUES ( Product[Category] )
RETURN
    SUMX ( 
        CategoriesVisible,
        CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ) )
    )

Now the total row is the sum of the row-level distinct counts, matching the expectation, at the cost of counting the same customer multiple times if they appear in multiple categories.

Cause #4: Filters, Row-Level Security, and Hidden Context

Sometimes the total looks wrong because you’re not seeing all the filters that apply.

Common situations:

• Visual-level filters applied to the matrix but not obvious at first glance.
• Page or report filters that change total context.
• Row-Level Security (RLS) limiting visible rows.

In our scenario, suppose there is a page filter Region = "West". A stakeholder compares the total row to a separate export that includes all regions. The numbers will differ, and the total will be blamed.

Fix Pattern: Inspect Filter Context Explicitly

To debug, create a helper measure that shows which filters are active:

Debug Filters = 
VAR Regions = CONCATENATEX ( VALUES ( Customer[Region] ), Customer[Region], ", " )
VAR Years   = CONCATENATEX ( VALUES ( 'Calendar'[Year] ), 'Calendar'[Year], ", " )
RETURN
    Regions & " | Years: " & Years

Place this in a card or table next to the matrix while debugging. It helps reveal unexpected filters affecting totals.

For RLS, test with View as in Power BI Desktop to see the report exactly as the stakeholder sees it.

Cause #5: Measures Referencing Other Measures with Different Granularity

A subtler source of confusion is when a measure references another measure that is itself non-additive.

Example:

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

Weighted Margin % = 
VAR ProductsVisible = VALUES ( Product[ProductID] )
RETURN
    AVERAGEX ( ProductsVisible, [Margin %] )

At the total row, Weighted Margin % averages Margin % at the product level, not at the customer or segment level. If the matrix rows are customers, this can produce totals that look inconsistent.

Fix Pattern: Align the Granularity

Always align the granularity used inside VALUES with the visual’s row grain.

If the matrix rows are Customer[CustomerID], redefine:

Weighted Margin % (By Customer) = 
VAR CustomersVisible = VALUES ( Customer[CustomerID] )
RETURN
    AVERAGEX ( CustomersVisible, [Margin %] )

Now the total row is the average of customer-level margin %, which matches the matrix.

Practical Debugging Checklist for Wrong Totals

When a total row “doesn’t add up”, walk through this checklist:

1. Is the measure additive?

   • Sums of amounts are additive.
   • Ratios, percentages, averages, distinct counts are not.

2. What grain is the visual using?

   • Customer, product, segment, date?
   • Does your measure’s logic match that grain?

3. What is the total supposed to represent?

   • Weighted ratio (total numerator / total denominator)?
   • Average of row-level ratios?
   • Sum of row-level values?

4. Which filters are active?

   • Check visual, page, report filters.
   • Consider RLS and cross-filtering from other visuals.

5. Are you referencing other non-additive measures?

   • Inspect dependencies and ensure granularity alignment.

Once you answer these, you can decide whether the total is actually wrong or just different from the expectation, and then choose the appropriate pattern.

Applying the Fix to Our Scenario

Back to the sales performance report.

The manager wants:

• Row-level: Achievement % per customer-year.
• Total: average of customer Achievement %, not weighted by sales.

We implement:

Achievement % (Avg of Customers) = 
VAR CustomersVisible = VALUES ( Customer[CustomerID] )
RETURN
    AVERAGEX ( 
        CustomersVisible,
        VAR NetSalesCustomer = CALCULATE ( [Net Sales] )
        VAR TargetCustomer   = CALCULATE ( [Target] )
        RETURN 
            DIVIDE ( NetSalesCustomer, TargetCustomer )
    )

Result:

• Each customer-year row still shows its own achievement ratio.
• The total row now shows the average of those ratios across customers, matching the manager’s expectation.

If finance later asks for a weighted overall achievement for the same matrix, we can add a second measure:

Achievement % (Weighted) = DIVIDE ( [Net Sales], [Target] )

And clearly label both in the report:

• Achievement % (Avg of Customers)
• Achievement % (Weighted)

Same data, two different total behaviours, both technically correct.

One Takeaway: Make Total Behaviour Explicit in Your Measures

When the total row doesn’t add up, it’s almost always because DAX is doing exactly what it’s designed to do: recompute the measure under a different filter context.

The practical fix is simple but non-negotiable: design your measures with explicit total behaviour. Decide whether you want a weighted ratio, an average of row-level values, or a sum of rows, and then implement that pattern intentionally instead of relying on default aggregation.