Sum customers by product combination buyed a+b, a+c, b+c, or a+b+c or no products at all

[KingCanadian]

Hi there,

having a list of customers with the info about the products buyed.

In my example there are 3 products - but in real there are 50-100 products.

How can i get the Sum of customers who buy the same product combination.
e.g. if i want to know what are the top10 product combinations buyed by one customer.
over all and by country.

Is Power BI the right tool to do this?

[dj9090]

Thanks for this solution!

I have another question. How can you add the sum of the products within this solution?

For example: product A = $10, B = $5 and C = $20.

Product combination A,C = $30, A,B,C = $35 and so on.

Is it possible to add this this as an additional column?

[v-shex-msft · Accepted solution]

Hi @KingCanadian,

You can take a look a below formual to get the Combinations.

Table:

Table Formula:

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

Then you can use TOPN function or topn filter to get the specify data.

TopN function:

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

TOPN(10,SUMMARIZE(temp,[combinations],"Number",COUNTAX(FILTER(temp,[combinations]=EARLIER([combinations])),[Customer])),[Number],ASC)

TopN filter:

Regards,

Xiaoxin Sheng

[jare56]

Hi,

Your post was great, it helped me a lot. I wanted to ask you, if I would need to insert another column what change should I do

[KingCanadian]

Thank you very much for this solution!

[KingCanadian]

Hi there,

having a list of customers with the info about the products buyed.

In my example there are 3 products - but in real there are 50-100 products.

How can i get the Sum of customers who buy the same product combination.

e.g. if i want to know what are the top10 product combinations buyed by one customer.

over all and by country.

Is Power BI the right tool to do this?

[BhaveshPatel]

Hi @KingCanadian,

R clustering visual will be the best fit for the the requirement. See the screenshot.

Or

Alternatively, You can visit this BLOG to create something similar using DAX.