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

# Quartile Sales & Bucketing

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 Person Number of Units Total Value Avg. Effort Score Lebron 5 470000 650 Rose 7 650000 675 Kelly 4 350000 700 Tina 8 855000 725 Drake 1 175000 625 Lil Wayne 10 1020500 500

Sales Person Quartile

 Sales Person Number of Units Total Value Avg. Effort Score Lebron 3 3 2 Rose 2 3 2 Kelly 3 4 1 Tina 1 1 1 Drake 4 4 3 Lil Wayne 1 1 4

Sales Person Ranking

 Sales Person Ranking Lebron 2.67 Rose 2.33 Kelly 2.67 Tina 1.00 Drake 3.67 Lil Wayne 2.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

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

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