Forum Discussion
Dynamic top quartile
Hi Everyone
I would appreciate if someone could help me with a problem that I have been grappling with for a week.
I have a table which looks like this:-
| customer_id | gender | location | division | product | accessory | purchase_date | sales_amount |
| 1 | M | A | XXX | XXX-1 | XXX-1-1 | 2017-02-04 | 841 |
| 2 | F | B | YYY | YYY-1 | YYY-1-4 | 2017-10-06 | 839 |
| 4 | F | C | XXX | XXX-2 | XXX-2-3 | 2017-11-08 | 765 |
| 2 | F | B | ZZZ | ZZZ-2 | ZZZ-2-1 | 2017-08-01 | 647 |
| 3 | M | A | XXX | XXX-2 | XXX-2-5 | 2017-07-27 | 590 |
| 4 | F | C | YYY | YYY-2 | YYY-2-3 | 2017-06-10 | 543 |
| 2 | F | B | ZZZ | ZZZ-2 | ZZZ-2-5 | 2017-07-16 | 767 |
| 2 | F | B | ZZZ | ZZZ-2 | ZZZ-2-3 | 2017-03-02 | 897 |
| 5 | F | B | ZZZ | ZZZ-3 | ZZZ-3-5 | 2017-06-13 | 891 |
| 4 | F | C | XXX | XXX-1 | XXX-1-3 | 2017-08-02 | 779 |
| 5 | F | B | YYY | YYY-3 | YYY-3-3 | 2017-06-30 | 952 |
| 3 | M | A | ZZZ | ZZZ-3 | ZZZ-3-5 | 2017-06-03 | 714 |
| 6 | F | A | YYY | YYY-1 | YYY-1-2 | 2017-01-24 | 746 |
| 3 | M | A | XXX | XXX-3 | XXX-3-2 | 2017-11-27 | 543 |
| 4 | F | C | YYY | YYY-1 | YYY-1-1 | 2017-05-03 | 843 |
| 6 | F | A | ZZZ | ZZZ-1 | ZZZ-1-2 | 2017-07-10 | 502 |
| 3 | M | A | XXX | XXX-1 | XXX-1-1 | 2017-03-03 | 905 |
| 4 | F | C | XXX | XXX-1 | XXX-1-5 | 2017-11-11 | 936 |
| 5 | F | B | ZZZ | ZZZ-1 | ZZZ-1-5 | 2017-05-09 | 532 |
The report I'm creating will have the following slicers:-
| Location |
| Gender |
| Division |
| Product |
| Month |
I would like to see customers who fall in the top quartile by Division and Product.
When I select a particular Division, and all products in the respective slicers, it should select top quartile customers for that Division.
When I select a particular Division and Product, it should select top quartile for the combination of Division and Product.
When I select all divisions, it should select top quartile customer for all divisions combined.
And for the customers that fall in the top quartile based on the above selection, I would like to show the following:-
- Sales by Division
- Sales by Product
- Sales by Location
- Sales by Accessory
- Sales by Month
Thanks
2 Replies
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
