Count (measure) is not providing correct total

[Anonymous]

Hello, 

I have created a simple measure (DISTINCTCOUNT) and used in both in card and table but getting wrong totals:

Total should be 2,278 not 1947. Could you pls provide any idea? 

[TomMartens · Accepted solution]

Hey @Anonymous ,

I assume that DISTINCTCOUNT returns the correct result, unfortunately the result does not meet your expectation.

I'm not sure what table[column] you are using inside the DISTINCTCOUNT function.

Assuming that you are using products as row headers and DISTINCTCOUNTing customers who bought the product. It will be likely that a customer has bought more than one product.

In the total line there is no implicit product filter, nevertheless, a customer who just bought more than one product will be counted only once, as this is the nature of the DISTINCTCOUNT function.

If you want to overcome this you can use this measure instead (be aware that the result on the total line is not a DISTINCTCOUNT)

measure = 
SUMX(
VALUES('tablename'[columnname]) //used as rowheader
, CALCULATE( DISTINCTCOUNT( 'tablename'[columnname] ) ) //the column you want to DISTINCTCOUNT
)

 Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Thanks @TomMartens , that was a quick fix.