Dynamic top quartile

[massvyas]

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

[Phil_Seamark]

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?

[massvyas]

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