Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
6 REPLIES 6
New Member

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?

Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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

New Member

Thank you very much for this solution!

New Member

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?

Community Champion

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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors