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,

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Thank you for your message.

Please correct me if I missed something from your question, but is it separated by month? or by team? How many separared tables do you have? 

If it is OK with you, could you please show how the each table looks like?

Thank you.

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,