Forum Discussion
Quartile Analysis
Hi,
I'm working to create quartiles based on an average measure. My dataset is structured as follows:
Date Table with two columns: [Date] and [Week End]
Customer Table with four columns: [User ID] [Store ID] [Total Sales] [Week End]
Many:One Relationship between Customer Table [Week End] and Date Table [Date]
I have a measure in the Customer Table to calculate the # of Unique User IDs each week:
Unique Customers = CALCULATE(SUMX(VALUES('Customers'[UserID]),1))
I have another measure to tell me the number of stores each week:
Weekly Reporting Units = CALCULATE(SUMX(Values('Customers'[Company ID]),1))
Finally, a third measure to tell me the # of unique customers per store:
Unique Customers / Unit = DIVIDE('Daycare Customers'[Unique Customers],[Weekly Reporting Units])
I am struggling with the output of my quartile measure to identify the top quartile of Unique Customers / Unit. My current measure is:
Top Quartile = PERCENTILEX.INC('Customers','Customers'[Unique Pet Parents / Unit],.75)
The table returned is below:
| Week End | Unique Customers / Unit | Top Quartile |
| 3/5/22 | 173.78 | 1.00 |
| 3/12/22 | 173.54 | 1.00 |
| 3/19/22 | 170.33 | 1.00 |
| 3/26/22 | 175.26 | 1.00 |
| 4/2/22 | 173.62 | 1.00 |
| 4/9/22 | 176.44 | 1.00 |
| 4/16/22 | 172.99 | 1.00 |
| 4/23/22 | 176.85 | 1.00 |
| 4/30/22 | 176.87 | 1.00 |
| 5/7/22 | 178.41 | 1.00 |
| 5/14/22 | 169.92 | 1.00 |
| 5/21/22 | 172.01 | 1.00 |
I have no idea why the quartile calculation is not returning the appropriate values. Any suggestions?
2 Replies
jackj I did a bunch of stuff for quartiles here that might help:
Thanks for publishing! The issue I'm having though is that I'm looking to find the quartile ranges for a measure, not a column - does that change the DAX at all?
