Forum Discussion

renecornish's avatar
renecornish
Regular Visitor
2 years ago
Solved

Ranking Across Columns

I have distance travelled data between various locations ~ stipulated in individual columns.  I am now wanting to rank which location is first, second, third closest (or less in distance).  How do I rank theses?  I can't seem to Group the columns as I was hoping to use the RANK.EQ formaul which works in excel and not here.

 

Example:  Location 1 is positioned 3rd closest...

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you for your prompt reply Greg_Deckler 

    Hi renecornish     

     

    Based on your needs, I have created the following table.

     

    Then in power query, hold down the Ctrl key to select all columns, click "unpivot column".


    Click"close and apply", Then you can use the following Dax to get the rank of the column:

    Column = RANKX('Table','Table'[Value],,DESC)

     

    Result:



    Best Regards,

    Jayleny

     

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    renecornish Probably have to do something along these lines:

    Column = 
  VAR __Table = 
    UNION(
      { [Location 1] },
      { [Location 2] },
      { [Location 3] },
      { [Location 4] },
      { [Location 5] },
      { [Location 6] }
    )
...

    Then you could use RANKX and then lookup the RANK for whatever value [Location 1] is for your Location 1 rank column, etc.

  • renecornish's avatar
    renecornish
    Regular Visitor
    2 years ago

    Thank you Greg, however when I use this (as above) - it runs an error.  "A table of multiple values was supplied where a single value was expected".

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you for your prompt reply Greg_Deckler 

    Hi renecornish     

     

    Based on your needs, I have created the following table.

     

    Then in power query, hold down the Ctrl key to select all columns, click "unpivot column".


    Click"close and apply", Then you can use the following Dax to get the rank of the column:

    Column = RANKX('Table','Table'[Value],,DESC)

     

    Result:



    Best Regards,

    Jayleny

     

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