Forum Discussion

Stu11's avatar
Stu11
Frequent Visitor
4 years ago
Solved

Ranking Data

Hi, I'm trying to add a rank to the below table in PBi. Each colum (Month,Team&Emplyee,Sumber of solves are in seperate tables)   The rank would need to rank each employee by team by month. So e...
General Comment
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Number of solves: = 
SUM ( Data[Number Of Solves] )

     

    Ranking per Month and per Team: = 
VAR employeetable =
    ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )
RETURN
    IF (
        HASONEVALUE ( Employee[Employee] ) && NOT ISBLANK ( [Number of solves:] ),
        RANKX ( employeetable, [Number of solves:],, DESC )
    )
  • Stu11's avatar
    Stu11
    4 years ago

    Hi,

     

    In your example:

     

    "ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )"

     

    I don't have a table named "data". All the tales are joined using a relationship. 

    Do I need to look at Crossjoin in order to get this to work?

     

    Thanks,

Stu11's avatar
Stu11
Frequent Visitor
4 years ago

Hi again, thank you for your time so far.

 

I guess what I'm asking for is what you've done so far but as a measure. If that is even possible.

DimCalendar[Month]

Company[Team]/Company[Emplyee]

Data[Number of Solves]

Each of the table have a relatopship between them.

 

Hope this makes sense. 

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

Number of solves: = 
SUM ( Data[Number Of Solves] )

 

Ranking per Month and per Team: = 
VAR employeetable =
    ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )
RETURN
    IF (
        HASONEVALUE ( Employee[Employee] ) && NOT ISBLANK ( [Number of solves:] ),
        RANKX ( employeetable, [Number of solves:],, DESC )
    )
  • Stu11's avatar
    Stu11
    Frequent Visitor
    4 years ago

    Hi,

     

    I have copied this and with my version the rank is only ever 1 or blank.


    Not sure as to why. I will try and investigate.

     

    Thanks,

    • Stu11's avatar
      Stu11
      Frequent Visitor
      4 years ago

      Hi,

       

      Cannot seem to figure out why it's not working. Any other suggestions?

       

      Thanks,

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please share your sample pbix file, and then I can try to have a look into it to come up with a more accurate solution.

        Thanks.