Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking with multiple columns

I have the below data and I need to calculate the 3 red highlighted colums. I was able to get Salesrep ranking with Rankx(THis is all data ranking). But others are not working within all this data co...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello,

 

I actually need to create other reports in a similar way so I was duplicating it, but cannot understand what you did, Is there an article explaining it? I need to understand below code(mainly the role of MAX,why and what is that doing here). Thank you so much

 

RANKX(
FILTER(
ALL('Security Data'),
'Security Data'[Region]=Max('Security Data'[Region])&& [Total Services]>0),
[Total Services],,DESC,Dense
v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you