Search code examples
powerbidax

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

Solution

  • 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