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,

 

Got it working using a summazried table.

 

"

Solved/Closed Ticket Ranking =
VAR Currentmonth = 'Summary Table'[MonthYear]
VAR Currentteam = 'Summary Table'[Team]
VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)

RETURN

rankx(newtable,'Summary Table'[Numer of Solved/Closed Tickets],,DESC,Dense)"
 
Is the code above how could I make it so the rank ignore blanks?
 
Thanks,
 
Stu