Forum Discussion

natabird3's avatar
natabird3
Icon for Skilled Sharer rankSkilled Sharer
6 years ago
Solved

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 b...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
6 years ago

can you please try the below measure to get 

 

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

 

 

  • natabird3's avatar
    natabird3
    Icon for Skilled Sharer rankSkilled Sharer
    6 years ago

    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's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.