Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
natabird3
Skilled Sharer
Skilled Sharer

Rank 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:

CountryStart DateEnd DatePrice Discount (Y/N)On-Site/Off-SiteCost (EUR)Rank
United Kingdom12/2/20195/3/2019NoOn-Site €             100,0001
China8/21/20199/1/2019YesOn-Site €               95,0002
China8/21/20199/1/2019YesOn-Site €               90,0003
China8/21/20199/1/2019YesOn-Site €               85,0004
United Kingdom12/2/20195/3/2019NoOn-Site €               80,0005
China8/21/20199/1/2019YesOn-Site €               75,0006
China8/21/20199/1/2019YesOn-Site €               70,0007
China8/21/20199/1/2019YesOn-Site €               65,0008
China8/21/20199/1/2019YesOn-Site €               60,0009
China8/21/20199/1/2019YesOn-Site €               55,00010
United States of America2/1/20192/14/2019NoOn-Site €               50,00011
China1/1/20191/31/2019YesOn-Site €               45,00012
China8/21/20199/1/2019YesOn-Site €               40,00013
China8/21/20199/1/2019YesOn-Site €               35,00014
China8/21/20199/1/2019YesOn-Site €               30,00015
China8/21/20199/1/2019YesOn-Site €               25,00016
China8/21/20199/1/2019YesOn-Site €               20,00017
China8/21/20199/1/2019YesOn-Site €               15,00018
China1/1/20191/31/2019YesOff-Site €               10,00019
China1/1/20191/31/2019YesOff-Site €                 5,00020

 

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?

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

 

 

aa14.PNG

Or like this?

 

 

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

 

aa15.PNG

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.

View solution in original post

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

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
Super User
Super User

can you please try the below measure to get 

 

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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. 

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
)

 

 

aa14.PNG

Or like this?

 

 

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

 

aa15.PNG

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.