Why does a distinct-customer grand total differ from the sum of the regional rows?

Example Answer

I'd check whether the same customer can appear in more than one region. Suppose North has customers A and B, while South has B and C. Each regional row correctly shows two customers, but the overall distinct count is three. Adding the rows gives four because it counts B twice.

The total evaluates the distinct-count measure in the total's filter context; it does not simply add the displayed cells. I'd explain that before changing the DAX, because the smaller total may be the correct business answer.

Then I'd confirm whether stakeholders want unique customers or the sum of regional customer counts. If they want the latter, I'd provide a separately named metric and explain that it counts regional memberships. I'd validate both with this small overlap case and with customers who appear in only one region.

Make it your own

Use an overlap example with customers, orders, or employees. Confirm whether the business entity can legitimately belong to multiple groups.

Why this works

Shows that a surprising total can be correct and protects the metric's meaning instead of forcing arithmetic agreement.

Interviewer follow-up

Could a missing customer ID affect DISTINCTCOUNT?

Yes. DISTINCTCOUNT includes a BLANK value, so missing IDs can contribute a distinct value. I'd inspect those rows before deciding whether to exclude them. DISTINCTCOUNTNOBLANK excludes blanks, but that changes the metric; I'd document whether unidentified transactions belong in the report and track the missing-ID issue separately.

References

• Microsoft reference 1
• Microsoft reference 2