Forum Discussion
How to create equally sized quintiles, sorted by second variable?
I have a database of customers. Customer id, frequency and recency. Table looks like this. Frequency and Recency are calculated columns.
The code I already have looks like below. The problem is that this splits the quintiles based on the sum of frequency. So the first quintile only has a few customer ids in it and the bottom one a lot. What I like to achieve is quintiles with equal numbers of customers but ranked by frequency. (it's also for quartiles but will fix that).
Quartile (Frequencies) 2 =
VAR Frequency =
SUM ( 'shopifyCustomers'[Frequency] )
VAR CustomerList =
GROUPBY (
ALL ( 'shopifyCustomers' ),
'shopifyCustomers'[Customer Id],
"Val", SUMX ( CURRENTGROUP (), [Frequency] )
)
VAR Percentile50 =
MEDIANX ( CustomerList, [Val] )
VAR Percentile75 =
MEDIANX ( FILTER ( CustomerList, [Val] >= Percentile50 ), [Val] )
VAR Percentile25 =
MEDIANX ( FILTER ( CustomerList, [Val] < Percentile50 ), [Val] )
RETURN
IF (
Frequency >= Percentile75,
"Q4",
IF ( Frequency >= Percentile50, "Q3", IF ( Frequency >= Percentile25, "Q2", "Q1" ) )
)
12 Replies
Hi aukev
Could you show me with a screenshot how it looks like when you add “Quartile (Frequencies) 2” in the table visual?
“quintiles with equal numbers of customers”
Does this mean the quintiles show the same value for the same numbers of customer id?
“ranked by frequency”, could you show me an example of your expected result?
Best regards
Maggie
Thank you for your help. What I`m seeing now is this:
The Q1 is missing but more importantly the quartiles are segmented based on the sum of the frequency.
What I`m looking for is as shown below. Where the quartiles are segmented by the number of customer ids within them.
v-juanli-msft or anyone else, still looking for a solution to this. Any help would be awesome :)
