How many customers have bought same product combination

[ChPetru]

Hi guys,

Need help with calculation how many customers have bought the same combination of products.

In the below example both customers A and D have purchased same products (Products: 1,2,3 and 4).

How can I find this count in PowerBI? My first thought is by creating a key for each unique product combination (not sure how the dax code would look for that) and then simply do customer count for each of those keys,.

Customer	Product
Customer A	Product 1
Customer A	Product 2
Customer A	Product 3
Customer A	Product 4
Customer B	Product 5
Customer B	Product 6
Customer B	Product 7
Customer B	Product 8
Customer B	Product 2
Customer C	Product 3
Customer C	Product 4
Customer C	Product 5
Customer C	Product 6
Customer D	Product 1
Customer D	Product 2
Customer D	Product 3
Customer D	Product 4

Please help!

[mh2587 · Accepted solution]

Summary Table =
var temp= SUMMARIZE(Sheet5,Sheet5[Customer],"Combinations",CONCATENATEX(FILTER(SUMMARIZE(Sheet5,[Customer],Sheet5[Type]),Sheet5[Customer]=EARLIER(Sheet5[Customer])),[Type]&","))
return
SUMMARIZE(temp,[combinations],"Number",COUNTAX(FILTER(temp,[combinations]=EARLIER([combinations])),[Customer]))

Modify It According to your needs

[ChPetru]

Hi mh2587, thank you for the help, it works, however I am now unable to link the summarized table to the existing fact table (to be able to set filters on date or bring some other columns like the customer name for some investigation). Any idea on how I acomplish this?

[mh2587]

would you please share the model

[ChPetru]

apologies, the data is confidential but here's how the two tables look in the model:

I have the summary table you suggested and need a way to see all the Opportunities that belong under each "combination".

Also, I will need to filter the Summary table by a specific "Opportunity Close Date", column that belongs to the fact table.

Is there a way I can make a relationship between the two tables? Or make the summary table as columns on the fact table?

[ChPetru]

thank you, it works! now only thing i have to do is find a way to link it to the other tables 🙂

[mh2587]

Check this one hope it will suit your requirements 

https://www.daxpatterns.com/basket-analysis/