Forum Discussion
Dynamic top quartile
Hi massvyas
Can you please provide a sample result based on say selecting Division XXX
Oh and what is the pseudo formula that determines if a customer is in the top quartile?
Thanks for responding! I've tried to explain the logic below for division XXX.
1. Assuming all filters are off, the first step would be calculating total sales for each customer of XXX. That would look like this:-
| customer_id | total sales |
| 1 | 841 |
| 3 | 2038 |
| 4 | 2480 |
2. Next, we calculate the 75th percentile. Using Excel's PERCENTILE.INC, it comes to 2259.
3. Customers in the top quartile are ones whose total sales are greater than 2259, which is only customer_id 4 for XXX.
4. The result would be as follows:-
Sales by Division
| Division | Total sales |
| XXX | 2480 |
Sales by Product
| Product | Total sales |
| XXX-1 | 1715 |
| XXX-2 | 765 |
Sales by Location
| Location | Total Sales |
| C | 2480 |
Sales by Accessory
| Accessory | Total Sales |
| XXX-1-3 | 779 |
| XXX-1-5 | 936 |
| XXX-2-3 | 765 |
Sales by Month
| Month | Total Sales |
| Aug-17 | 779 |
| Nov-17 | 1701 |
5. Selection of any of the filters would only change the results displayed, but not the top quartile customers selected. For e.g, if I filter for the month of November, it would still be customer_id 4 because the 75th percentile remains at 2259. Results would be as follow:-
Sales by Division
| Division | Total sales |
| XXX | 1701 |
Sales by Product
| Product | Total sales |
| XXX-1 | 936 |
| XXX-2 | 765 |
Sales by Location
| Location | Total Sales |
| C | 1701 |
Sales by Accessory
| Accessory | Total Sales |
| XXX-1-5 | 936 |
| XXX-2-3 | 765 |
Sales by Month
| Month | Total Sales |
| Nov-17 | 1701 |