RANKX as a calculated column DAX measure

[BlueWhite3699]

Hi Experts

How would you rank the following using a calculated column formula. See sample data

Score	Department	Ranking
10	Training	3
11	Training	2
15	Training	1
22	Consultancy	3
27	Consultancy	2
30	Consultancy	1
1	Sales	1
2	Sales	2
11	Marketing	2
12	Marketing	1

[v-priyankata]

Hi @BlueWhite3699 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[AntrikshSharma]

@BlueWhite3699 Ranking for sales is wrong in your example, you can use any of these.

Ranking = 
RANK ( 
    DENSE, 
    ALL ( DepartmentScores[Score], DepartmentScores[Department] ), 
    ORDERBY ( DepartmentScores[Score], DESC ), 
    PARTITIONBY ( DepartmentScores[Department] ) 
)

Ranking = 
VAR CurrentScore = 
    DepartmentScores[Score]
RETURN
    CALCULATE ( 
        COUNTROWS ( DepartmentScores ),
        REMOVEFILTERS ( DepartmentScores[Score] ),
        DepartmentScores[Score] >= CurrentScore
    )

Ranking = 
VAR CurrentScore = 
    DepartmentScores[Score]
VAR CurrentDepartment = 
    DepartmentScores[Department]
VAR Result = 
    COUNTROWS ( 
        FILTER ( 
            DepartmentScores, 
            DepartmentScores[Department] = CurrentDepartment 
                && DepartmentScores[Score] >= CurrentScore 
        )
    )
RETURN
    Result

[Shivu-2000]

Hii @BlueWhite3699,

I recommend first trying the solution provided by @mdaatifraza5556 .

If the issue still persists, here is a slightly modified version that might help resolve it.

Ranking =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Department] = EARLIER('Table'[Department])
&& 'Table'[Score] > EARLIER('Table'[Score])
)
) + 1

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[mdaatifraza5556 · Accepted solution]

Hi @BlueWhite3699 

Can you please try the below DAX ?

Ranking =

RANKX(

    FILTER(

        'Tables',

        Tables[Department] = EARLIER(Tables[Department])

    ),

    Tables[Score],

    ,

    DESC,

    DENSE

)

If this answers your questions, kindly accept it as a solution and give kudos.