CODEWITHSUNDEEP
powerbidax
Puneeth
Puneeth

Reputation: 33

Power BI Distinct Count Incorrect Total

I am trying to calculate Distinct Count of 'Cust' in Table A using DAX as

CALCULATE(DISTINCTCOUNT('TableA'[Cust]))

Where I'm getting the distinct count correctly but the Total is Incorrect.

Any suggestions on correcting the formula would be helpful. Thanks!!

Upvotes: 1

Views: 9002

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

Let's say you have four customers "Alex","John","Mike","Joe" in three BT groups AUT,CT,MT.

AUT has "Alex","John","Mike"

CT has "John","Joe"

MT has "Alex","John","Mike","Joe"

Your table would be

BT   Count
----------
AUT  3
CT   2
MT   4

Now since you have only four total customers, I would expect your total to be 4, not 3 + 2 + 4 = 9. In the latter, you've double counted Alex, Joe, and Mike and triple counted John.

If you really do want your total to be the sum of the subtotals (though I don't see why you would want this in this case), you can do the following:

AddSubtotals = SUMX(VALUES('TableA'[BT]), CALCULATE(DISTINCTCOUNT('TableA'[CustomerName])))

This will give the weird 9 subtotal.

Bad Subtotal

Upvotes: 2

Related Questions