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.
hi, I'm new with power bi . How I can do ranking for sum price of orders.
I have table "orders" which have column "product" and "price" , the product column have duplicated values with diferent price, I need sum all price by product and then rank them.
Product | Total Charge | Rank |
ProdAAA | 150 | 1 |
ProdBBB | 100 | 2 |
ProdCCC | 80 | 3 |
ProdAA | 150 | 1 |
ProdBB | 130 | 1 |
ProdCC | 120 | 3 |
Thanks!!
Solved! Go to Solution.
if it's a column range
Rank á RANKX(ALL(orders), orders[price],,desc,dense)
if it's a range of measurement
Treat
Total charge : SUM(orders[price]) // or similar formula
Then
Range Measurement -Range - RANKX(ALL(orders[Product]), [Total Charge],,desc,dense)
Or
Range Measurement -Range - RANKX(ALL(orders[Product]), [Total Charge],,desc,dense) + rand()/1000
For range See these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Hi,
What result do you get with these measures?
Total price = SUM(Orders[Price])
Rank = rankx(all(Orders[product]),[Total price])
if it's a column range
Rank á RANKX(ALL(orders), orders[price],,desc,dense)
if it's a range of measurement
Treat
Total charge : SUM(orders[price]) // or similar formula
Then
Range Measurement -Range - RANKX(ALL(orders[Product]), [Total Charge],,desc,dense)
Or
Range Measurement -Range - RANKX(ALL(orders[Product]), [Total Charge],,desc,dense) + rand()/1000
For range See these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Total Charge = SUM(orders[price])
then
Rank Measure = RANKX(ALL(orders[Product]), [Total Charge],,desc,dense)
its works !! thanks !
can you explain please why I need use orders[Product] in ALL function ? because whe I put only table name ALL(orders) its dosen't works...
Hi,
You are welcome. You must use ALL(Orders[Product]) because you have dragged Product to your visual.
If my previous reply helped, please mark it as Answer.
@kimalto452 - You can use RAND() to break ties:
Rank = RANKX( ALL(orders),SUMX(orders,orders[price])*(RAND()/100))
Also, you don't need CALCULATE there.
This might also help - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Finally there is also this: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528
this dosen't work
Rank = RANKX( ALL(orders),SUMX(orders,orders[price])*(RAND()/100))
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |