Rank with the actual values

[Smits474]

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.

 

[v-yulgu-msft]

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]

 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.'

[MattAllington]

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]

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

[MattAllington]

sorry, that was a silly mistake.  Try this

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

[Smits474]

Same problem. And i only get the values of the person who is selected in my slicer, but i want all the other persons too. Maybe the top n function is better to use?

[MattAllington]

I tested this measure. If you put people on rows of a table, and a slicer on the type, it will give you the rank for each person.