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.

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

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.