Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ranking/Grouping based on conditions

Table1

 

Agent  

Duration(s)DateRank

A

273      01/06/2020 
A26301/06/2020 
A22401/06/2020 
A14602/06/2020 
A15903/06/2020 
A15304/06/2020 
B8801/06/2020 
B18402/06/2020 
B2802/06/2020 
B24602/06/2020 
B28603/06/2020 
B5504/06/2020 
B3804/06/2020 
C28702/06/2020 
C14302/06/2020 
C8402/06/2020 
C3402/06/2020 
C28003/06/2020 
C17904/06/2020 
C8005/06/2020 
D11201/06/2020 
D12701/06/2020 
D25401/06/2020 
D15002/06/2020 
D21603/06/2020 
D7704/06/2020 
D2805/06/2020 
D20506/06/2020 
E18501/06/2020 
E22401/06/2020 
E27501/06/2020 
E26102/06/2020 
E23103/06/2020 
E5704/06/2020 
E1505/06/2020 
E15805/06/2020 

 

I have something similar to the table above. What I need is to split the agents into 4 buckets. The buckets should be ranked from 1 to 4 based on the average of the durations per agent, but only taking the durations before 04/06/2020. 

 

The idea is I want to split the agents into quartiles based on the best to the worst. 1 being the best (shortest avg duration) to 4 (longest average duration).

 

I can't seem to get the calculations to work. I basically need the Rank column as a calculated column, not a measure as I want the value stored on the table.

 

 

Need Help
Show and Tell
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    I directly excluded the rows greater than April 6th when calculating the average. 

     

    In addition, you can also change the sorting mechanism, for example, when [__AVG]= Blank() then [__Rank]=0

     

    Best regards,
    Lionel Chen

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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous - See this: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

     

    Also, check out the PERCENTILE.INC, PERCENTILE.EXC and the X equivalents of those functions. They are designed to split things into percentages and you can use them as quartiles. 

     

    Also, check out my series on Excel to DAX translation as it has additional information about those functions as well as QUARTILE and  PERCENTRANK equivalent for DAX.

    https://community.powerbi.com/t5/Community-Blog/P-Q-Excel-to-DAX-Translation/ba-p/1061107

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks.

       

      Ive tried using percentile. So I have done it in a few steps

      1. Calculated an avg per agent =AVGperAgent= CALCULATE(AVERAGE(table1[duration(s)]),ALLEXCEPT(table1[agent]),table1[date]<DATE(2020,06,22))

      2. rank the agents by quartile = Rank =
      var low = PERCENTILE.INC(table1[AVGperAgent],.25)
      var mid = PERCENTILE.INC(table1[AVGperAgent],.5)
      var high = PERCENTILE.INC(table1[AVGperAgent],.75)
      RETURN
      IF(table1[AVGperAgent]<=low,1,IF(table1[AVGperAgent]<=mid,2,IF(table1[AVGperAgent]<=high,3,IF(table1[AVGperAgent]>high,4,0))))
      This should give me an equal distribution of rank counts so very similar counts of each rank, however I'm not getting this. Is there something missing here?


      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Is this not what you want?

        If yes, please refer to my .pbix file, if not, please show me the expected output value with a table.

         

        Best regards,
        Lionel Chen

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