Rank dynamically the data displayed in a table based on the sorted data in the table visual

[natabird3]

Hello,

Easy question i hope, if i have table where i have displayed some values and rows of data (without summarizing it), is there a way to add a column that shows the number of rows displayed based on the way i sort the data. So for example:

Country	Start Date	End Date	Price Discount (Y/N)	On-Site/Off-Site	Cost (EUR)	Rank
United Kingdom	12/2/2019	5/3/2019	No	On-Site	€             100,000	1
China	8/21/2019	9/1/2019	Yes	On-Site	€               95,000	2
China	8/21/2019	9/1/2019	Yes	On-Site	€               90,000	3
China	8/21/2019	9/1/2019	Yes	On-Site	€               85,000	4
United Kingdom	12/2/2019	5/3/2019	No	On-Site	€               80,000	5
China	8/21/2019	9/1/2019	Yes	On-Site	€               75,000	6
China	8/21/2019	9/1/2019	Yes	On-Site	€               70,000	7
China	8/21/2019	9/1/2019	Yes	On-Site	€               65,000	8
China	8/21/2019	9/1/2019	Yes	On-Site	€               60,000	9
China	8/21/2019	9/1/2019	Yes	On-Site	€               55,000	10
United States of America	2/1/2019	2/14/2019	No	On-Site	€               50,000	11
China	1/1/2019	1/31/2019	Yes	On-Site	€               45,000	12
China	8/21/2019	9/1/2019	Yes	On-Site	€               40,000	13
China	8/21/2019	9/1/2019	Yes	On-Site	€               35,000	14
China	8/21/2019	9/1/2019	Yes	On-Site	€               30,000	15
China	8/21/2019	9/1/2019	Yes	On-Site	€               25,000	16
China	8/21/2019	9/1/2019	Yes	On-Site	€               20,000	17
China	8/21/2019	9/1/2019	Yes	On-Site	€               15,000	18
China	1/1/2019	1/31/2019	Yes	Off-Site	€               10,000	19
China	1/1/2019	1/31/2019	Yes	Off-Site	€                 5,000	20

This table is sorted by the spend and the rank is displayed accordingly. I would like to add another column say sales, and if i sorted by that column the rank column to update for that sales column. Is this possible?

[v-lionel-msft]

Hi @natabird3 ,

Your formula for [Rank] column should look like this:

Sales_Rank= RANKX(ALL(test[name]),[Spend],,DESC,Dense)

If you want to sort the Sales column, the formula should look like this:

Sales_Rank= RANKX(ALL(test[name]),[sales],,DESC,Dense)

You can find that RANKX() function only sorts a certain column, and it can't achieve dynamic changes.

But you can implement multi-column sorting:

Sorting a table using multiple columns 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

can you please try the below measure to get 

Sales_Rank= RANKX(ALL(test[name]),[sales],,DESC,Dense)

[natabird3]

Hello,

Thanks for the reply. Unforutantely, i have already tried a few of these rankx options but the desired result is not coming. Jut so i understand the "test name" is the dimension that i am trying to use, so like country for instance, and the [sales] is the value that i want to rank, which is a measure say sum of sales. I put all that in a measure and all i get is 1 and 2 for most values but not an accuarate ranking at all. Any idea why is that happening? Thanks in advance for the help. 

[v-lionel-msft · Accepted solution]

Hi @natabird3 ,

Sorting multiple columns using the RANK () function?

Like this?

//column
Rank = 
RANKX(
    FILTER(
        Sheet7,
        Sheet7[Country] = EARLIER(Sheet7[Country])
    ),
    Sheet7[Cost (EUR).2],
    , ASC, Dense
)

Or like this?

//Column
Rank(1) = 
RANKX(
    Sheet7,
    [Sum(cost)],
    ,ASC,Dense
)

If these are not correct,  Please give us an output table (made in Excel) and indicate whether each column is roriginal data or comes from a DAX formula?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.