cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support

Hi @KingCanadian,

 

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

Table:

Capture.PNG

 

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

 

Capture2.PNG

 

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:

Capture3.PNG

 

Regards,

Xiaoxin Sheng

 

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

View solution in original post

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

Hi @KingCanadian,

 

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

Table:

Capture.PNG

 

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

 

Capture2.PNG

 

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:

Capture3.PNG

 

Regards,

Xiaoxin Sheng

 

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

View solution in original post

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

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?

 

 

Hi @KingCanadian,

 

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

 

Clustering.PNG

 

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors