Forum Discussion
Most sold product
Hello,
I am trying to do some customer analysis with a transactional data set.
I would like the ability to draw from all of the individual transaction lines a table somethin like this:
Customer Name | Most bought product
The way I would define the Most bought product column is the product with the highest sales value.
For example:
If Joe Blogs bought the following off me:
£100 on Carrots
£50 on potatoes
£75 on Steak
£150 on Lobster
I would want the table to show
Joe Bloggs | Lobster
Thank you for your help
Hey,
I would use the DAX function TOPN: https://dax.guide/topn/ for example this measure:
TOP 1 Product = CONCATENATEX( TOPN( 1 , VALUES('Product'[ModelName]) , CALCULATE(SUM('Reseller Sales'[OrderQuantity])) ) , [ModelName] , ", " )Please be aware that, there are multiple products, that have been the same "value", that you are using for the comparison.
Hopefully this provides some ideas, to slove your requirement.
Regards,
Tom
2 Replies
- TomMartens
Super User
Hey,
I would use the DAX function TOPN: https://dax.guide/topn/ for example this measure:
TOP 1 Product = CONCATENATEX( TOPN( 1 , VALUES('Product'[ModelName]) , CALCULATE(SUM('Reseller Sales'[OrderQuantity])) ) , [ModelName] , ", " )Please be aware that, there are multiple products, that have been the same "value", that you are using for the comparison.
Hopefully this provides some ideas, to slove your requirement.
Regards,
Tom
- Cmcmahan
Resident Rockstar
What do you want to do in the case of a tie?
MostPurchasedProduct = CALCULATE( SELECTEDVALUE(Products[ItemName]), TOPN(1, ADDCOLUMNS( VALUES(Products[ItemName]), "Total Spent", CALCULATE(SUM(Products[Amt Spent]))), [Total Spent], DESC))
