Quartile Analysis

[jackj]

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?

[jackj]

@Greg_Deckler 

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?

[Greg_Deckler]

@jackj I did a bunch of stuff for quartiles here that might help:

QUARTILE - Microsoft Power BI Community

QUARTILE.EXC - Microsoft Power BI Community