Forum Discussion
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 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
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 ) )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,
12 Replies
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 )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?
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.
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)RETURNrankx(newtable,'Summary Table'[Number of Tickets],,DESC,Dense)"Thanks,StuHi 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)RETURNrankx(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