Power BI Distinct Count Incorrect Total

1
votes

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!!

1
powerbidax
What total do you expect? It's possible that you have the same customer in multiple BT categories, in which case the total should not be the sum of the parts. - Alexis Olson
@AlexisOlson I'm expecting the total 232 yes Same Customers can be there for multiple BT's - Puneeth
So you want to double count those customers in your total? The current total only counts each customer once. - Alexis Olson
sorry I didn't understand what is double count!! but since the sub totals as seen in the image is returning correct Distinct Count respective to BT.. hence i want the total of all subtotals as my grand total. - Puneeth

1 Answers

2
votes

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