RANKX duplicated values, showing only 1 or showing duplicate ranking

[kimalto452]

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

Rank = RANKX(ALL(orders), SUMX(orders,orders[price])) 

with this measure I recive all 1

with this code I recive duplicated rank for diferent price

Rank = RANKX( ALL(orders), CALCULATE(SUMX(orders,orders[price])))

for example

ProdAA	150	1
ProdBB	130	1
ProdCC	120	3

  

Thanks!!

[Ashish_Mathur]

Hi,

What result do you get with these measures?

Total price = SUM(Orders[Price])

Rank = rankx(all(Orders[product]),[Total price])

[amitchandak · Accepted solution]

@kimalto452 ,

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...

[kimalto452]

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...

[Ashish_Mathur]

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.

[Greg_Deckler]

@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

[kimalto452]

this dosen't work

Rank = RANKX( ALL(orders),SUMX(orders,orders[price])*(RAND()/100))