Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get Second and Third values in a Rank

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's avatar
    Jihwan_Kim
    4 years ago

    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] )
        ) & ")"

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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] )
        ) & ")"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It worked, thanks a lot!!

    • udnemesis's avatar
      udnemesis
      New Member
      3 years ago

      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] )
        ) & ")"