Ranking

[Anonymous]

Hi there,

This is my very first post here in the community, so I hope I'm doing it in the right way 🙂

I need some help with creating a dax formula for the table "Commodity" , that shows the rank for "Physical Commodity" based on the "Contracted Quantity", ignoring the impact of the "Date", like below:

"Contracted Quantity" is a measure, and "Date" and "Physical Commodity" are part of different tables.

Can you give me some suggestions? 

Thanks

[Dangar332]

hi, @Anonymous 

try below measure  

rank1 =

RANK(SKIP,ALL('rank'),ORDERBY('rank'[contracted quantity],ASC))

[Anonymous]

@Dangar332  @Jihwan_Kim  thanks for trying.

I manage to solve it with Calculate and All functions.

Have a nice day!

[Dangar332]

hi,  @Anonymous 

adjust your table name
try below one
result =
var a = SUMMARIZECOLUMNS('physical commodity'[physical commodity],"cont commodity",[contracted commodity])

var b = SELECTCOLUMNS(a,"rankcomparing",[cont commodity])
return 

rankx(b,[rankcomparing],[Contracted Quantity])

'physical commodity' -- is your table name so adjust it .

[Contracted Quantity]  -- is your measue

[Anonymous]

Hi @Dangar332,

[contracted commodity] from variable "a", I believe should be "contracted quantity", right?

In the form with "contracted quantity" the formula does not show anything 😞

Thanks

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Contract Qty: = 
SUM( sales[Contracted Quantity] )

Ranking by physical commodity = 
RANK (
    SKIP,
    ALL ( 'Physical Commodity'[Physical Commodity] ),
    ORDERBY ( [Contract Qty:], DESC )
)

[Anonymous]

Hi @Jihwan_Kim ,

Thanks for the suggestion, but it does not work 😞

If I add "Date" to the the table it's messing up with the rank.

Thanks,

Georgiana