Ranking Data

[Stu11]

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 each team would have it's own sperate rank each month.

Tried doing this a couple of times to no avail. Any help would be appreciated.

Month	Team	Employee	Number of solves	Rank
April	Team1	Person1	20
April	Team2	Person2	30
April	Team3	Person3	48
April	Team4	Person4	65
April	Team5	Person5	70
April	Team1	Person6	2
April	Team2	Person9	3
May	Team3	Person3	4
May	Team4	Person4	5
May	Team5	Person5	6
May	Team1	Person7	7
May	Team2	Person2	8
May	Team3	Person8	69
May	Team4	Person10	56
June	Team5	Person5	26
June	Team1	Person1	695
June	Team2	Person2	23
June	Team3	Person3	5
June	Team4	Person4	545
July	Team5	Person5	5647

Thanks

[Jihwan_Kim · Accepted solution]

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]

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

[Stu11]

Hi again,

Got it working by creating a summary table. Using your code earlier. 

Quick question, how can i make so the ranking ignore blanks (within the "

'Summary Table'[Number of Tickets]" column?

"

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'[Number of Tickets],,DESC,Dense)"

Thanks,

Stu

[Jihwan_Kim]

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]

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]

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]

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 · Accepted solution]

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]

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]

Hi,

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

Thanks,

[Jihwan_Kim]

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 · Accepted solution]

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]

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