cancel
Showing results for
Did you mean:
New Member

## Count (measure) is not providing correct total

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?

1 ACCEPTED SOLUTION
Super User II

Hey @Jess90 ,

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(
, CALCULATE( DISTINCTCOUNT( 'tablename'[columnname] ) ) //the column you want to DISTINCTCOUNT
)``````

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
2 REPLIES 2
Super User II

Hey @Jess90 ,

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(
, CALCULATE( DISTINCTCOUNT( 'tablename'[columnname] ) ) //the column you want to DISTINCTCOUNT
)``````

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
New Member

Thanks @TomMartens , that was a quick fix.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.