topic Re: Ranking in DAX Commands and Tips

Ranking

Anonymous — Sat, 14 Oct 2023 20:09:29 GMT

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

Re: Ranking

Jihwan_Kim — Sun, 15 Oct 2023 04:56:31 GMT

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

Re: Ranking

Anonymous — Mon, 16 Oct 2023 10:31:13 GMT

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

Re: Ranking

Dangar332 — Mon, 16 Oct 2023 12:07:20 GMT

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

Re: Ranking

Anonymous — Tue, 17 Oct 2023 07:39:58 GMT

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

Re: Ranking

Dangar332 — Wed, 18 Oct 2023 06:42:48 GMT

hi, Anonymous

try below measure

rank1 =

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

Re: Ranking

Anonymous — Wed, 18 Oct 2023 10:05:49 GMT

Dangar332 Jihwan_Kim thanks for trying.

I manage to solve it with Calculate and All functions.

Have a nice day!