Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
massvyas
Frequent Visitor

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_idgenderlocationdivisionproductaccessorypurchase_datesales_amount
1MAXXXXXX-1XXX-1-12017-02-04841
2FBYYYYYY-1YYY-1-42017-10-06839
4FCXXXXXX-2XXX-2-32017-11-08765
2FBZZZZZZ-2ZZZ-2-12017-08-01647
3MAXXXXXX-2XXX-2-52017-07-27590
4FCYYYYYY-2YYY-2-32017-06-10543
2FBZZZZZZ-2ZZZ-2-52017-07-16767
2FBZZZZZZ-2ZZZ-2-32017-03-02897
5FBZZZZZZ-3ZZZ-3-52017-06-13891
4FCXXXXXX-1XXX-1-32017-08-02779
5FBYYYYYY-3YYY-3-32017-06-30952
3MAZZZZZZ-3ZZZ-3-52017-06-03714
6FAYYYYYY-1YYY-1-22017-01-24746
3MAXXXXXX-3XXX-3-22017-11-27543
4FCYYYYYY-1YYY-1-12017-05-03843
6FAZZZZZZ-1ZZZ-1-22017-07-10502
3MAXXXXXX-1XXX-1-12017-03-03905
4FCXXXXXX-1XXX-1-52017-11-11936
5FBZZZZZZ-1ZZZ-1-52017-05-09532

 

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 2
Phil_Seamark
Microsoft Employee
Microsoft Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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_idtotal sales
1841
32038
42480

 

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

DivisionTotal sales
XXX2480

 

Sales by Product

ProductTotal sales
XXX-11715
XXX-2765

 

Sales by Location

LocationTotal Sales
C2480

 

Sales by Accessory

AccessoryTotal Sales
XXX-1-3779
XXX-1-5936
XXX-2-3765

 

Sales by Month

MonthTotal Sales
Aug-17779
Nov-171701

 

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

DivisionTotal sales
XXX1701

 

Sales by Product

ProductTotal sales
XXX-1936
XXX-2765

 

Sales by Location

LocationTotal Sales
C1701

 

Sales by Accessory

AccessoryTotal Sales
XXX-1-5936
XXX-2-3765

 

Sales by Month

MonthTotal Sales
Nov-171701

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.