Forum Discussion

rrafferty37's avatar
rrafferty37
Helper 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. 

rrafferty37's avatar
rrafferty37
Helper I
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.