topic Re: Rank Measure according to categories in DAX Commands and Tips

Rank Measure according to categories

Power_MN — Wed, 10 Mar 2021 10:54:08 GMT

Hello everybody, I am faced with a problem with which I cannot make any progress, because I have not found a solution after many researches. So I hope that one of you can help me with this. Rank = RANKX(Table1,Table1[Quality],,DESC,Dense) -> this function doesn't work for me I have a dataset (see table). This should be ranked according to supplier and product. After that, I should be able to have a ranking for each product, showing which supplier is the best and which is the worst. Supplier Product Quality Rank A chair 80% 2 A desk 90% 1 B bed 100% 1 C chair 75% 3 B desk 40% 3 A bed 40% 3 C desk 85% 2 B chair 100% 1 C bed 65% 2 Thank you for your help!

Re: Rank Measure according to categories

amitchandak — Wed, 10 Mar 2021 11:52:07 GMT

Power_MN , this is a column rank

Try measure rank

Rank = RANKX(allselected(Table1[product]) ,calculate(Sum(Table1[Quality])),,DESC,Dense)

For Rank Refer 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

or my video -https://www.youtube.com/watch?v=DZb_6j6WuZ0

Re: Rank Measure according to categories

Power_MN — Wed, 10 Mar 2021 12:17:31 GMT

amitchandak Many thanks for your response. Unfortunately the DAX function does not work. It gives me rank 1 for every row. Kind regards Michael

Re: Rank Measure according to categories

amitchandak — Wed, 10 Mar 2021 12:55:30 GMT

Power_MN , measure rank is context-sensitive. if you add any other than product like product id, it will become 1.

Geography Rank = RANKX(all(Geography[City]),[Sales])
Geography Rank = RANKX(all(Geography),[Sales])
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])

In these examples with city id , first one will give rank 1 only.

Try to give table name to combination of columns

Re: Rank Measure according to categories

Power_MN — Wed, 10 Mar 2021 13:26:34 GMT

Hello amitchandak,

have you got an advice, how I can do it right?

Thanks a lot!

Re: Rank Measure according to categories

amitchandak — Wed, 10 Mar 2021 13:50:55 GMT

Power_MN , this is column rank. I suggested measure.

Try like this

rankx(filter(table, [supplier] =earlier([supplier] )), [quality],,desc, dense)

Also, refer to link I shared

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Re: Rank Measure according to categories

Power_MN — Thu, 11 Mar 2021 14:01:59 GMT

amitchandak the content on your link was very helpful. The solution was: Rank = Rankx(Filter(Table,Table1[Product]=EARLIER(Table1[Product])),Table1[Quality],,DESC,Dense) I want to complete this Dax-function with a second category "Product Group". So the ranking has the be grouped by "Product" AND "Product Group". How can i complete a second "Filter" to the ranking DAX-function? Thank you so much! Kind regards