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,

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.

      • Stu11's avatar
        Stu11
        Frequent Visitor
        4 years ago

        Hi,

         

        I believe the issue is I don't have a table named "data" where all this data resides. My data is in 3 sperate tables (although they do have a relationship). 

        Thanks,

         

        Stu