Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |