SAP Webi Ranking Function- smallest number to be the 1st in the rank

[former_member787075]

Hi there,

I have multiple stores with a 5 different measurement categories with % and each column has a ranking based on the these percentage performance. i.e. 5 measures with 5 corresponding ranking columns.

I need to find out a national-wide rank based on these 5 different rankings so I made a cumulative rank column which adds all of 5 columns ranking. Then I made a new column called "National-Wide Rank" i.e =Rank([Cumulative Rankings]). Finally I sorted this column by Ascending order and it worked perfectly fine.

However, I realised this logic is wrong because it is ranking and ascending based on the largest cumulative number from the "cumulative ranking column" and show as a 1st in national wide which is incorrect. A Store with highest cumulative number should be the worst performing store.

For example,

e.g.

Store A: Rank 1 Rank 2 Rank 3 Rank 4 Rank 5 Cumulative Rank National Wide Rank

3 4 8 9 11 35 2

Store B: Rank 1 Rank 2 Rank 3 Rank 4 Rank 5 Cumulative Rank National Wide Rank

8 12 15 15 5 55 1

As per the table above, Store B (with the larger cumulative rank) is showing as rank 1 which it shouldn't be.

The correct logic should be: the store with lowest cumulative rank to be the 1st in National Wide Rank as per the example below.

Store A: Rank 1 Rank 2 Rank 3 Rank 4 Rank 5 Cumulative Rank National Wide Rank

3 4 8 9 11 35 1

Store B: Rank 1 Rank 2 Rank 3 Rank 4 Rank 5 Cumulative Rank National Wide Rank

8 12 15 15 5 55 2

So What formula should I use to make the "National-Wide Rank" column to look on numbers in :"Cumulative Rank" and return the smallest cumulative rank number as a 1st and largest cumulative rank to show as last. In other word, "Ascending order" but the lowest number should be the 1 st and so on.

Thanks

[ayman_salem]

use

v_Rank: =Rank([Cumulative Rankings]; Bottom).

[former_member787075]

Hi Ayman,

This is awsome. Thank you very much!

Cheers

[ayman_salem]

if the answer is OK, kindly accept it and close the question.