Forum Discussion

rrafferty37's avatar
rrafferty37
Icon for Helper I rankHelper I
6 years ago
Solved

Dynamic Quartiles

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...
Need Help
Tips and Tricks
Tutorial Requests
  • rrafferty37's avatar
    rrafferty37
    6 years ago

    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's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

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

)