2 Replies Latest reply on Jul 31, 2018 2:14 PM by Farbod Azizi

    Quartile Sales & Bucketing

    Farbod Azizi

      Hi Everyone,

       

      I am attempting to rank our sales department based on various categories. For each category the sales person will be placed into a quartile group (1 - Top; 4 - Bottom). At the end, I want to get an average score for each category to give each person a ranking. A simple example below:

       

      Sales Production

      Sales PersonNumber of UnitsTotal ValueAvg. Effort Score
      Lebron5470000650
      Rose7650000675
      Kelly4350000700
      Tina8855000725
      Drake1175000625
      Lil Wayne101020500500

       

      Sales Person Quartile    

      Sales PersonNumber of UnitsTotal ValueAvg. Effort Score
      Lebron332
      Rose232
      Kelly341
      Tina111
      Drake443
      Lil Wayne114

       

       

      Sales Person Ranking

       

      Sales PersonRanking
      Lebron2.67
      Rose2.33
      Kelly2.67
      Tina1.00
      Drake3.67
      Lil Wayne2.00

       

       

      What is the best way to generate this output? I am thinking some type of formula... Ultimately, this will be referencing a sql database and will need to calculate this as a monthly view. Hope I provided enough information for what I am trying to do. Look forward to the collaboration.

       

      Thanks,

        • 1. Re: Quartile Sales & Bucketing
          Ken Flerlage

          I'm guessing that this stems from your previous post (Quartile based on a Percentile table) where you were using RANK_PERCENTILE. If so, is that how you'd intend to establish the quartiles? I'm going to assume so, but if that's the case, I should note that your quartiles above do not match the quartiles which would be calculated using RANK_PERCENTILE.

           

          Start by doing your ranking. You'll need 3 calculated fields--one for each of your 3 measures. Here's an example for Units:

           

          Rank Units

          RANK_PERCENTILE(SUM([Number of Units]))

           

          Then you can use this to determine your quartiles.

           

          Quartile Units

          IF [Rank Units]>=.75 THEN

              1

          ELSEIF [Rank Units]>=.5 THEN

              2

          ELSEIF [Rank Units]>=.25 THEN

              3

          ELSE

              4

          END

           

          I won't include them here, but you'd create calcs like these for Total  Value and Avg. Effort Score.

           

          Then you'd create a new calculated field which averages these values:

           

          Ranking

          ([Quartile Effort]+[Quartile Units]+[Quartile Value])/3

           

          Then you can add them to your view. The calcs will be table calculations, so make sure they are each set to compute using Sales Person.

           

           

          Here's the final result:

           

           

          I've attached my sample Excel data source and the packaged workbook.

          • 2. Re: Quartile Sales & Bucketing
            Farbod Azizi

            Thank you Ken Flerlage! Everything worked perfectly. Much appreciated.