cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jess90
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:

Jess90_0-1606948983932.png

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

 

1 ACCEPTED SOLUTION
TomMartens
Super User II
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(
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



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

View solution in original post

2 REPLIES 2
TomMartens
Super User II
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(
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



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

View solution in original post

Thanks @TomMartens , that was a quick fix.

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors