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 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!

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. 

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community 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",

    )

  • 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.