Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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
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))
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
User | Count |
---|---|
136 | |
73 | |
73 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |