Ranking with multiple columns

Hi, Anonymous 

Measures:

_Super Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[ Super Region ]=MAX('Table2'[ Super Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[Region ]=MAX('Table2'[Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Total Services = SUM('Table2'[ Security Total  ])

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

Hi, Anonymous 

The max function here is used to get the value of the current row. In the current row context, since there is only one row, no matter you use the MAX, MIN, or SELECTEDVALUE functions, it has the same effect.

Now to explain the above formula, first we look at the syntax of the RANKX function:

So

_Super Region Ranking =
IF(
    [_Total Services] <= 0,
    0,
    RANKX(
        FILTER(
            ALL( 'Table2' ),
            'Table2'[ Super Region ] = MAX( 'Table2'[ Super Region ] )
                && [_Total Services] > 0
        ),
//Get all records in the same region and rank them in a sub-table of all rows in this same region.
//Filter the sub-tables whose region in the entire table is equal to the region of the current row.
        [_Total Services],
        ,
        DESC,
        DENSE
    )
)

Hope this helps you understand.

Best Regards,
Community Support Team _ Zeon Zheng

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