Forum Discussion
Skilled SharerRank dynamically the data displayed in a table based on the sorted data in the table visual
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?
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- rajendraongole1
Super User
can you please try the below measure to get
Sales_Rank= RANKX(ALL(test[name]),[sales],,DESC,Dense)
natabird3Hello,
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-msftCommunity Support
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- 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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
