topic Re: Count Customers who purchased more than X Units - Total not filtered in DAX Commands and Tips

Count Customers who purchased more than X Units - Total not filtered

etane — Mon, 22 Apr 2024 22:19:43 GMT

Hello.

This seems to be a very easy DAX but somehow I haven't found the solution.

Here's a copy of a table I am working on:

I just need to count the number of customers who purchased more than 1 unit. The answer should be 6 in the example above. But, the total is currently summing all the customers which is 13. I tried various fashions of sumx and couldn't get anything closer than the if formula.

Any hints?

Thanks.

Re: Count Customers who purchased more than X Units - Total not filtered

gmsamborn — Mon, 22 Apr 2024 23:00:09 GMT

Hi etane

Would a measure like this help?

Customers who bought multiple units = COUNTROWS( FILTER( SUMMARIZE( 'Table', 'Table'[Accounts], "_Units", SUM( 'Table'[Units Sold] ) ), [_Units] > 1 ) )

Let me know if you have any questions.

etane.pbix

(In the model I added DimAccount so I could fix the sort order.)

Re: Count Customers who purchased more than X Units - Total not filtered

etane — Mon, 22 Apr 2024 23:06:22 GMT

GS

Thanks! Works like a charm.

Re: Count Customers who purchased more than X Units - Total not filtered

Anonymous — Wed, 24 Apr 2024 13:46:17 GMT

Another option is calculate(distinctcount('Table'[Accounts]), 'Table'[Units Sold] > 1)