Ranking data with certain filters

[danialsj]

I have data that I want to rank with filters within the DAX formula. I need a DAX formula which lets me rank 'Branches' in the 'North' cluster for the month of 'October'.

Data Preview:
Branch     Score       Month     Cluster
1001         70             Sept       North
1002         76             Sept       South
1003         77             Sept       North
1004         72             Sept       North

1005         56             Sept       South
1001         74             Oct        North
1002         73             Oct        South
1003         78             Oct        North
1004         69             Oct        North
1005         54             Oct        South

Required:
Ranking of Branches of North cluster for October
1      1003      78
2      1001      74
3      1004      69


Please help!

[Ashish_Mathur]

Hi,

These are the measures I used

Scores = SUM(Data[Score])

Measure = RANKX(ALLSELECTED(Data[Branch]),[Scores])

Hope this helps.

[Icey · Accepted solution]

Hi @danialsj ,

You can create your measure like so:

Rank = 
RANKX (
    ALLSELECTED(  'Table' ),
    CALCULATE ( MAX ( 'Table'[Score] ) ),
    ,
    DESC,
    DENSE
)

 

Best Regards,
Icey

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