Get Second and Third values in a Rank

[Anonymous]

Hi,

I have a table that contains data about logins by employees. An employee can have multiple rows per day.

My report is filtering by day and I need to show in my report 3 cards. The first one should show the name of the employee with more logins, the second one the second employee with more logins, and in the third, the third employee with more logins.

I have been trying with ranks, but I'm not able to get a good result.

For instance, if I select day 1:

Card 1 should show C (9)

Card 2 should show A (7)

Card 3 should show D (2)

For instance, if I select day 4:

Card 1 should show D

Card 2 should show B

Card 3 should show A

Thanks in advance for your help.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Ranking: =
RANKX ( ALL ( Employee[Employee] ), CALCULATE ( SUM ( Data[Logins] ) ),, DESC )

Rank one: = 
VAR ranktable =
    FILTER (
        ADDCOLUMNS(
            VALUES(Employee[Employee]),
            "@rank", [Ranking:]
        ),
        [@rank] = 1
    )
RETURN
    MAXX ( ranktable, Employee[Employee] ) & " ("
        & CALCULATE (
            SUM ( Data[Logins] ),
            Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
        ) & ")"

Rank two: = 
VAR ranktable =
    FILTER (
        ADDCOLUMNS(
            VALUES(Employee[Employee]),
            "@rank", [Ranking:]
        ),
        [@rank] = 2
    )
RETURN
    MAXX ( ranktable, Employee[Employee] ) & " ("
        & CALCULATE (
            SUM ( Data[Logins] ),
            Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
        ) & ")"

Rank three: = 
VAR ranktable =
    FILTER (
        ADDCOLUMNS(
            VALUES(Employee[Employee]),
            "@rank", [Ranking:]
        ),
        [@rank] = 3
    )
RETURN
    MAXX ( ranktable, Employee[Employee] ) & " ("
        & CALCULATE (
            SUM ( Data[Logins] ),
            Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
        ) & ")"

[udnemesis]

I was struggling with the same issue, thanks @Jihwan_Kim . Your solution worked like a baby. But I managed to make it a single measure instead of three with below code. I had to create a table named "Rank" to acheive it though. "Rank" table had a single column "Ranks" with values from 1 to 3.

Rank one: = 
VAR ranktable =
    FILTER (
        ADDCOLUMNS(
            VALUES(Employee[Employee]),
            "@rank", [Ranking:]
        ),
        [@rank] = SELECTEDVALUE(Rank[Ranks])
    )
RETURN
    MAXX ( ranktable, Employee[Employee] ) & " ("
        & CALCULATE (
            SUM ( Data[Logins] ),
            Employee[Employee] = MAXX ( ranktable, Employee[Employee] )
        ) & ")"

[Anonymous]

It worked, thanks a lot!!