DAX DISTINCTCOUNT Total Is Wrong? When the Total Is Actually Correct

A DAX DISTINCTCOUNT total often looks wrong because the
grand total is not the sum of visible row counts. DAX reevaluates the
distinct count over the total’s filter context, so a customer, order, or
employee that appears in multiple groups is counted once at the
total.

That behavior is usually correct.

The overlap example

Suppose customers bought from three categories:

Category	Distinct customers
Bikes	120
Accessories	80
Clothing	50

The visible rows add to 250. But if 40 customers bought from more
than one category, the grand total can be 210.

The measure:

Unique Customers =
DISTINCTCOUNT ( Sales[CustomerKey] )

is evaluated separately for each category row. At the grand total,
the Category filter is absent, so each customer key across all
categories is counted once.

Microsoft’s
DISTINCTCOUNT documentation explicitly notes that
distinct-count totals are not additive and illustrates orders that
appear in multiple product categories.

Prove the overlap
before changing the formula

Create:

Sum of Visible Category Counts =
SUMX (
    VALUES ( Product[Category] ),
    [Unique Customers]
)

Then compare:

Unique Customers

with:

Sum of Visible Category Counts

The difference is the duplicate participation across category
groups:

Overlap Count =
[Sum of Visible Category Counts]
    - [Unique Customers]

This is not necessarily the number of overlapping customers because a
customer can appear in three or more categories and contribute multiple
excess counts. It is the amount of additive overcount.

Decide which
total the business actually wants

There are two legitimate questions:

1. How many unique customers exist across all
   categories?

   Use [Unique Customers].

2. What is the sum of the category-level unique-customer
   counts?

   Use the iterator pattern.

The second counts a multi-category customer once per category. It may
represent “category memberships” or “customer-category relationships,”
not unique people.

Name the measure accordingly:

Customer Category Participations =
SUMX (
    VALUES ( Product[Category] ),
    [Unique Customers]
)

Do not label that measure “Unique Customers.” The name should reveal
the grain.

Why the total
changes with the grouping column

If the visual groups by Region instead of Category:

SUMX (
    VALUES ( Geography[Region] ),
    [Unique Customers]
)

counts customer-region participations.

If it groups by Month:

SUMX (
    VALUES ( 'Date'[YearMonth] ),
    [Unique Customers]
)

counts customer-month participations.

These are different business metrics. An iterator does not merely
“fix the total”; it defines a new grain.

The SUM vs SUMX guide explains
why the iterator’s table is part of the measure definition.

BLANK is included by
DISTINCTCOUNT

DISTINCTCOUNT counts the BLANK value:

Unique Customers =
DISTINCTCOUNT ( Sales[CustomerKey] )

If Sales contains blank customer keys, BLANK can contribute one to
the result.

To exclude it:

Unique Known Customers =
DISTINCTCOUNTNOBLANK ( Sales[CustomerKey] )

Or filter it explicitly when additional logic is needed:

Unique Known Customers =
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerKey] ),
    Sales[CustomerKey] <> BLANK ()
)

Before excluding blanks, check whether they represent unmatched
dimension keys. The relationship model may be the real issue.

Count the correct key

Distinct-count errors often come from counting a label rather than a
stable identifier.

Avoid:

DISTINCTCOUNT ( Customer[CustomerName] )

Two different customers can share a name, and one customer’s name can
change.

Prefer:

DISTINCTCOUNT ( Sales[CustomerKey] )

or the appropriate dimension key, depending on the model and desired
inclusion of customers with no transactions.

Write down what one distinct value represents:

• a person;
• an account;
• an order;
• an order line;
• a session;
• a product;
• a case.

The DAX can be syntactically valid while counting the wrong
entity.

Fact key or dimension key?

These can differ:

Customers with Sales =
DISTINCTCOUNT ( Sales[CustomerKey] )

Visible Customers =
DISTINCTCOUNT ( Customer[CustomerKey] )

The fact-table version counts keys present in visible Sales rows. The
dimension version counts customer keys visible in the Customer table,
which can include customers with no sales depending on filter
propagation.

For “customers who purchased,” use the fact participation or a
measure that explicitly requires Sales rows.

For “customers in the selected segment,” the dimension count may be
correct.

Why the
distinct count is identical on every row

If [Unique Customers] repeats across categories, the
category filter may not reach Sales.

Test:

Visible Sales Rows =
COUNTROWS ( Sales )

Visible Customer Keys =
COUNTROWS ( VALUES ( Sales[CustomerKey] ) )

If neither changes by category, inspect:

• the Product-to-Sales relationship;
• the field used on the visual;
• filter direction;
• disconnected tables; and
• filter-removal logic.

Use Power BI
relationship not working or same value on every
row for those paths.

Why DISTINCTCOUNT
differs from Power Query

The two calculations may run at different stages and under different
rules:

• Power Query counts before the semantic model’s report filters.
• DAX evaluates after model relationships and filter context.
• transformations may trim, replace, or normalize values.
• text comparison and source collation can differ.
• a model relationship can introduce an unknown blank member.
• incremental refresh or source filters can change the rows
  loaded.

Export the exact visible keys from the same filtered state before
deciding which tool is wrong.

A diagnostic table for
totals

Build a table with:

Unique Customers =
DISTINCTCOUNT ( Sales[CustomerKey] )

Sales Rows =
COUNTROWS ( Sales )

Customer List Debug =
CONCATENATEX (
    TOPN (
        20,
        VALUES ( Sales[CustomerKey] ),
        Sales[CustomerKey]
    ),
    Sales[CustomerKey],
    ", "
)

Use the list only on a tiny test model. Compare which keys occur in
multiple groups. The overlap becomes visible instead of theoretical.

Distinct count in
a percent-of-total measure

Suppose:

Customer Share =
DIVIDE (
    [Unique Customers],
    CALCULATE (
        [Unique Customers],
        ALLSELECTED ( Product[Category] )
    )
)

Category percentages can add to more than 100% because the same
customer appears in multiple category numerators while the denominator
counts that customer once.

That is mathematically consistent with overlapping sets.

If stakeholders expect mutually exclusive percentages, the model
needs a rule assigning each customer to one category, or the metric
should count customer-category participations. The percent-of-total guide
covers denominator choices.

A decision checklist

Before replacing the total:

1. Identify the entity being counted.
2. Confirm the key is stable.
3. Decide whether BLANK should count.
4. Find overlap across the displayed groups.
5. Define whether the total means unique entities or group
   participations.
6. Choose the iterator grain only after that definition.
7. Rename the additive version to expose its grain.

The “wrong total” is often the only row protecting you from
double-counting.

Frequently asked questions

Why doesn’t
DISTINCTCOUNT add up in Power BI?

Because the same distinct value can appear in multiple visible
groups. Each row counts it within that group, while the grand total
counts it once across all groups.

How do I force
the total to sum the visible rows?

Iterate the grouping values with
SUMX ( VALUES ( Group[Column] ), [Distinct Count Measure] ).
Do this only when the intended metric is the sum of group-level
memberships.

Does DISTINCTCOUNT count
BLANK?

Yes. Use DISTINCTCOUNTNOBLANK or an explicit filter when
BLANK should be excluded.

Where can I test overlapping
sets?

Use the DAX
playground with a tiny customer-category table. Compare the unique
total, the sum of category counts, and the overlap amount.