
1. Re: Quartile Sales & Bucketing
Ken Flerlage Jul 30, 2018 4:40 PM (in response to Farbod Azizi)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 fieldsone 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.

Ranking.twbx 14.6 KB

Sales.xlsx 9.4 KB


2. Re: Quartile Sales & Bucketing
Farbod Azizi Jul 31, 2018 2:14 PM (in response to Ken Flerlage)Thank you Ken Flerlage! Everything worked perfectly. Much appreciated.