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 columns together. I even tried to remove some data and created dimension tables and then did crossjoin but still its not working(maybe I didnt do correct). Is it even possible to create a table like below. Please guide.

 

 

This image is just a sample data, data is more with all duplicate values in Region, Super Region,President and Sales Director. Salesrep names are unique.

 

 

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.

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you, it works.

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

    Hi, Anonymous 

     

    Do you want to get the ranking based on the Security Total value or the total Spend value? Notice that a region appears multiple times in the Super Region column, how to calculate their ranking? Is the % security spend column also the column that you want to add?

     

    Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.  

    It makes it easier to give you a solution.

     

     

    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 did replied to this post, not sure what happened to my reply. Sending you the sample data with results needed(bold cols). I want to rank with Security total only. The ones with 0 or -ve amount in security total has rank 0. I did correctly for salesrep ranking, Company Ranking = IF(OR('Calculations'[Total Services]=0,'Calculations'[Total Services]<0),0,RANKX(ALL('Security Data'),[Total Services])) Total Services is a measure for sum of security total.

       

       Region  Super Region  President  Sales Director  Super Region Ranking  SalesEep Ranking  SalesRep Name  Security Total   total Spend % Security Spend
      CAWestA3Tan35Rob           306,614          10,9378%
      CAWestA3Tan46Emma           246,627          10,9375486%
      FLSouthA6Med28Charlotte           126,653       779,8571%
      FLSouthA6Med410Jose               6,679       779,8571%
      GASouthA10Ryan17Mia           186,640       779,8571%
      GASouthA10Ryan39Sam             66,666       779,8571%
      MDEastA2Mill2     
                
                
                
                
                
                
                
                
      • v-angzheng-msft's avatar
        v-angzheng-msft
        Community Support
        4 years ago

        Hi, Anonymous 

        Try to create a measure like this:

        Company Ranking = 
IF([Total Services]<=0,0,RANKX(FILTER(ALL('Table'),'Table'[ Security Total  ]>0),[Total Services],,ASC,Dense))

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your reply. I need ranking by " security total" .  Below is the sample data with results in bold columns. Yes region is a subset for super region and it is ranking within each region. Kindly note that this is just a sample. The actual data is not like this i.e here each super region has four rows only but in acual data it is not fixed(East has 20 rows, south 50 rows etc), same goes for region, here region ranking is 1-2 but in actual data VA occurs 10 times so its ranking is 1-10. Also for -ve and 0 values in " security total", rank is 0. I was able to achieve Salesrep ranking with  - 

    Company Ranking = IF(OR('Calculations'[Total Services]=0,'Calculations'[Total Services]<0),0,RANKX(ALL('Security Data'),[Total Services])).
    Need help for Super Region Ranking, Region Ranking 
     Region  Super Region  President  Sales Director  Super Region Ranking   Region Ranking  SalesEep Ranking  SalesRep Name  Security Total   total Spend % Security Spend
    CAWestA3Tan315Rob           306,614          10,9378%
    CAWestA3Tan426Emma           246,627          10,9375486%
    FLSouthA6Med218Charlotte           126,653       779,8571%
    FLSouthA6Med4210Jose               6,679       779,8571%
    GASouthA10Ryan117Mia           186,640       779,8571%
    GASouthA10Ryan329Sam             66,666       779,8571%
    MDEastA2Mill212Sophia           800,000       212,853376%
    MDEastA2Mill3213Dave                  289       212,8530%
    NYCNorthA4Bro2112John               1,633       484,6740%
    NYCNorthA4Bro000Ava             (3,100)       484,6740%
    PANorthA9Brown1111Isabella               4,000       484,6741%
    PANorthA9Brown0 0Kevin                 (734)       484,6740%
    TXWestA8Tand113Amelia           426,588          10,9378%
    TXWestA8Tand224Ram           366,601          10,9378%
    VAEastA1Mil111Olivia        3,000,000       212,8531409%
    VAEastA1Mil4214Nick                  287       212,8530%

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your reply, As mentioned in my reply, I had already achieved company ranking(sales rep ranking). I need help with other two, as mentioned earlier "Need help for Super Region Ranking, Region Ranking ". 

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Community Support
      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you, it works

  • 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