Dynamic Quartiles

[rrafferty37]

Hello,

I am trying to do some simple RFM analysis.  I need to place a value for a specific customer into 1 of 4 buckets ( 1, 2, 3, 4). These correspond to their respective quartiles.  

I have tried a few things based on a number of threads, but I am not able to get the measures to work when I filter based on something like sales region (creates a new subset of customers that need to be recategorized).

Where I am now is I have 3 measures:

First Q = PERCENTILE.INC(Sheet1[# of Orders],.25)
Second Q = PERCENTILE.INC(Sheet1[# of Orders],.5)
Third Q = PERCENTILE.INC(Sheet1[# of Orders],.75)

These will update dynamically as I want. However, I am not sure what the measure to write is to categroize each customer into their respective quartile buckets. 

This is some of the sample data I am working with. Ideally the fourth column would be a measure that compares the # of orders to the calculated quartiles (the 3 measures I created above).  

A bit stuck, maybe I've been staring at this too long, but some outside help would be very much appreciated.

Thank you!

[rrafferty37 · Accepted solution]

Got it! 

Test = 
VAR FirstQ = 
    CALCULATE(
    PERCENTILE.INC(Sheet1[# of Orders],.25),
    ALLSELECTED(Sheet1))
VAR SecondQ =
    CALCULATE(
    PERCENTILE.INC(Sheet1[# of Orders],.5),
    ALLSELECTED(Sheet1))
VAR ThirdQ =
    CALCULATE(
    PERCENTILE.INC(Sheet1[# of Orders],.75),
    ALLSELECTED(Sheet1))
VAR ThisVal = 
    SELECTEDVALUE(Sheet1[# of Orders])

Return 
    if (
        ThisVal <= FirstQ, 4,
    if (
        Thisval > FirstQ && ThisVal <= SecondQ, 3,
    if (
        ThisVal > SecondQ && ThisVal <= ThirdQ,2,1)
    )
    )

Just in case anyone ever looks this up.  The less orders (bottom tier, they are ranked as a 4. The most orders (top quarter) are ranked as 1. 

[Nathaniel_C]

Hi @rrafferty37 ,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Bucket =

Switch(

           True(),

            [Number of Orders] >= [Third Q], "Third Quartile",

            [Number of Orders] >= [Second Q], "Second Quartile",

            [Number of Orders] >= [First Q], "First Quartile",

)