Forum Discussion
Helper IRanking within a group
Hello, I am trying to rank employees against each other within the same market they serve. I am currently unable to get anything to work correctly besides comparing all employees against one another.
My current formula is:
This is how I want the data to display in my dashboard:
| Market | Rank w/in Market | Total Achievement | Employee |
| Nashville | 1 | 98% | John Doe |
| Nashville | 2 | 42% | Tom Smith |
| Baltimore | 1 | 88% | Jane Doe |
| Baltimore | 2 | 77% | Sam Smith |
| Baltimore | 3 | 30% | Jack White |
Hi Ryanb2412 ,
Try a measure
RankEmployee = RANKX(FILTER(ALL('Table'[Market], 'Table'[Employee]),'Table'[Market] = MAX('Table'[Market])),CALCULATE(SUM('Table'[Total Achievement])))Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
6 Replies
- amitchandak
Super User
Ryanb2412 ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415 Hi Ryanb2412 ,
Total AR Rank = RANKX(Filter (ALL('EmployeeList'[Market]), EmployeeList[Market] = Max(EmployeeList[Market])),[Total Achievement]))
You can also see this blog
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Regards,
HN
Ryanb2412Anonymous
This just gives me all 1s for every employee when used as a measure or calculated column. Any idea why that could be still? Total Achievement is a measure also, rather than a column if that could play a factor.
Hi Ryanb2412 ,
Try a measure
RankEmployee = RANKX(FILTER(ALL('Table'[Market], 'Table'[Employee]),'Table'[Market] = MAX('Table'[Market])),CALCULATE(SUM('Table'[Total Achievement])))Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi Ryanb2412 ,
I suggest you follow the link
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
It is a 3 series blog on RankX
Regards,
HN
Rank By Groups
RANKX ( ALL ( Table[Employee] ), CALCULATE ( SUM ( Table[Total Achivment] ), ALLEXCEPT ( Table, Table[Employee], Table[Market] ) ), , DESC )
