How to create equally sized quintiles, sorted by second variable?

[aukev]

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" ) )
    )

[v-juanli-msft]

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

[aukev]

Hi @v-juanli-msft

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.

[aukev]

 @v-juanli-msft or anyone else, still looking for a solution to this. Any help would be awesome 🙂

[v-juanli-msft]

Hi @aukev

The Frequency column is different from the second screenshot and the first screenshot.

Does this column exsit in the table or Is it created by you?

How does this column generate?

Best Regards

Maggie

[aukev]

They are the same, this is just a mistype in my example. The frequency column is a calculated column.

[v-juanli-msft]

Hi @aukev

Your table is in this form, right?

[aukev]

Exactly

[v-juanli-msft]

Hi @aukev

A workaround

In Queries Editor, sort by "Frequency" on descending order, then sort by "Customer Id" descending, then create a index column from 1

then create a calculated column

1/2 = COUNT(Sheet6[Index])/2

1/4 = [1/2]/2

3/4 = 3/4*COUNT(Sheet6[Index])

Q = IF([Index]<=[1/2],IF([Index]<=[1/4],"Q4","Q3"),IF([Index]<=[3/4],"Q2","Q1"))

Best Regards

Maggie

[aukev]

Hi @v-juanli-msft, thanks but I don't think that will work. As I want to do the same for a different column in this table. I want to do this for both Frequency and Recency and I can only sort by one.

[v-juanli-msft]

Hi @aukev

How do you sort these columns, Power BI support sorting by multiple columns.

Look at the examples.

Sorting by multiple columns

Best Regrads

Maggie

[aukev]

Hi Maggie, sorry for the late reply (holidays). The table is sorted by date.

[aukev]

So it already exists.