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

[GrahamKnowles]

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

[Greg_Deckler · Accepted solution]

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))

[Anonymous · Accepted solution]

Attached.

Best

D

[GrahamKnowles]

That's great, thanks @Anonymous 

[Greg_Deckler · Accepted solution]

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))

[GrahamKnowles]

Thats great, thanks @Greg_Deckler