Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |