topic Re: DAX to count number of customers buying from more than 2 manufacturers in DAX Commands and Tips

DAX to count number of customers buying from more than 2 manufacturers

GrahamKnowles — Wed, 15 Apr 2020 13:22:57 GMT

I have a dataset of sales data, this includes transaction level data including fields for the manufacturer of the product sold.

I need to track which and how many customers have bought a product from 2 or more manufacturers in a given period (each month)

In excel i achieve this relatively simply by having a list of customers and sumif revenue by Manufacturer, and then count the number of manufacturers where revenue >0 by customer, then countif on the outcome of this (count number with 1 manufacturer, count number with 2 etc)

I'm trying to replicate in DAX but not making great progress.

I can get to a count of how many manufacturers a customer has bough from with a DAX measure:

Measure = Calculate(DISTINCTCOUNT(SALES[Manufacturer]),SALES[Revenue]>0)

but fail when i then need to get to a summarised data level - so how many of my customers returned 1,2,3,4,5 etc for the previous calculation.

Does anyone have any ideas?

Thanks

Re: DAX to count number of customers buying from more than 2 manufacturers

Greg_Deckler — Wed, 15 Apr 2020 14:16:36 GMT

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, you should be able to do something like:

Measure 2 = VAR __Table = SUMMARIZE( 'Sales' [Customer], "Measure",[Measure] ) RETURN COUNTROWS(FILTER(__Table,[Measure] > 1))

Re: DAX to count number of customers buying from more than 2 manufacturers

Anonymous — Wed, 15 Apr 2020 15:26:15 GMT

Attached.

Best

Re: DAX to count number of customers buying from more than 2 manufacturers

GrahamKnowles — Wed, 15 Apr 2020 16:30:57 GMT

Thats great, thanks Greg_Deckler

Re: DAX to count number of customers buying from more than 2 manufacturers

GrahamKnowles — Wed, 15 Apr 2020 16:31:36 GMT

That's great, thanks Anonymous