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,

I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

It is for creating a new column.

 

 

Rank Employee by Team and by Month CC =
VAR currentmonth = Data[Month]
VAR currentteam = Data[Team]
VAR newtable =
    FILTER ( Data, Data[Month] = currentmonth && Data[Team] = currentteam )
RETURN
    RANKX ( newtable, Data[Number Of Solves],, DESC )
Stu11's avatar
Stu11
Frequent Visitor
4 years ago

Hi,

The outcome is perfect. Only issue is that these fields are not in the same table. The relate to one another.

 

Table examples, Month,Team/Employee,Count of solved. This means I cannot create a new column.

 

Would I need to create a new table?

  • 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 )
    )