Ranking within a group

[Ryanb2412]

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: 

Total AR Rank = RANKX(ALL('EmployeeList'),[Total Achievement], , DESC)

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

[GaloyanTelman]

Rank By Groups

RANKX ( ALL ( Table[Employee] ),
    CALCULATE ( SUM ( Table[Total Achivment] ),
        ALLEXCEPT (
            Table,
            Table[Employee],
            Table[Market]
        ) ),
    ,
    DESC
)

[harshnathani]

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

[harshnathani]

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

[Ryanb2412]

@harshnathani 

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.

[harshnathani · Accepted solution]

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 Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@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/3...