Forum Discussion

Smits474's avatar
Smits474
Frequent Visitor
9 years ago

Rank with the actual values

Hey Guys,

 

I want to rank the name of persons. An example is given below.

I have different people with different types and different speeds on different dates and times.

I want to know which persons have the highest speed with the same type. 

I don't know how to use the rank function properly because if i use the max or min it returns 1 as value, but i want the actual speed of the person.

 

 

Need Help

7 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    9 years ago

    I'm not on my PC, but from memory. 

     

    Add a slicer and select one of the types

    Create a table and add the names

    write a measure like this. 

     

    Rank = rankx(all(tablename[name]),max(tablename[speed])). This should give the order (i.e. Rank). 

    If you just want the speed, just use

    =max(tablename[speed]) 

    • Smits474's avatar
      Smits474
      Frequent Visitor
      9 years ago

      When i use this rank, it only gives all the different persons rank 1

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        9 years ago

        sorry, that was a silly mistake.  Try this

         

        Rank = rankx(all(data[Name]),CALCULATE(max(data[Speed])))

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Smits474,

     

    Please try below formulas:

    Rank =
    RANKX (
        ALLEXCEPT ( TableRank, TableRank[Type] ),
        LASTNONBLANK ( TableRank[Speed], MAX ( TableRank[Date] ) ),
        ,
        DESC,
        DENSE
    )

MaxSpeed =
    CALCULATE ( MAX ( TableRank[Speed] ), ALLEXCEPT ( TableRank, TableRank[Type] ) )

    If you still have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

    • Smits474's avatar
      Smits474
      Frequent Visitor
      9 years ago

       I want to have multiple tables outcome, with different tops.

       

      I have made a new datasheet and i have made tables of what i want to be is the outcome.

       

      The datasheet is:

      :Datasheet

      In this situation, i sliced on the name 'Peter', type 'B' and date '15-02-2016'.

       

      For 1: I want to know the top 5 of speed of all time with the same gender & type as Peter. 

      1

      For 2: I want to know the top 3 of speed with the angle of all time with the same gender & type as Peter. 

       

      2

      For 3: I want to know the top 2 of speed of the previous measure off Peter. 

      3

       

      I want all the outcomes in one page, so top N filtering isn't a solution.

      I've tried the top N funtion but it gives me this error: 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'