cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
5 REPLIES 5
Frequent Visitor

## Sum Customers which have Product a+b, a+c, b+c, or a+b+c

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?

Super Contributor

## Re: Sum Customers which have Product a+b, a+c, b+c, or a+b+c

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Highlighted
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

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

Thank you very much for this solution!

Occasional Visitor

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

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.

Announcements

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 40 members 908 guests
Recent signins: