Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ranking/Grouping based on conditions

Table1   Agent   Duration(s) Date Rank A 273       01/06/2020   A 263 01/06/2020   A 224 01/06/2020   A 146 02/06/2020   A 159 03/06/2020   A 153 04/06/...
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.

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks, this is 95% of what I need. however, I noticed the blank rank values next to the agent where the Date is >20200603. How can we populate these rows with the ranking also? So essentially I want the ranking next to every customer field regardless of the date (but based on the date prior to 20200604)

     

     

    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      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.

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

        Hi Anonymous ,

         

        Has your problem been solved?

         

        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.