Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a Column to categorize by quartiles

Hi Everyone!, 

 

I'm new to the forum (and to PowerBI) and I have a question that I have been all day researching for but so far no luck, hope someone can help me! 

 

I have a table of employees, and there is a column with the hourly rate. I would like to add another column to categorize this hourly rates per quartiles, so we have the lower paid quartile, mid lower paid, mid upper paid and upper paid quartiles. I would like it to look like this in the table (can't add screenshots because confidential) : 

 

Hourly rate    Quartile

9.55                  Q1

10.10                Q1

10.15                Q2

12.10                Q3

13.05                Q4

etc. 

 

I tried calculating the quartiles as measures, with the PERCENTILE.INC function so I have three measures q1, q2 and q3, but then I don't seem to be able to categorize each value from the hourly pay column with those measures... 

 

Sorry if this has been already answered in another thread, I promise I did lots of research and didn't find anything that would work! 

 

Thanks in advanced! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    You can try to use the following measure formula if helps:

    Quartile =
VAR currRate =
    MAX ( Table[Hourly rate] )
VAR _min =
    MINX ( ALLSELECTED ( Table[Hourly rate] ), [Rate] )
VAR _max =
    MAXX ( ALLSELECTED ( Table[Hourly rate] ), [Rate] )
VAR interval =
    DIVIDE ( _max - _min, 4 )
VAR result =
    DIVIDE ( currRate - _min, interval )
RETURN
    IF ( result > INT ( result ), INT ( result ) + 1, result )

    Regards,

    Xiaoxin Sheng

5 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    Hi Susana

     

    Click here to download example solution 

     

    First you need to get the total for all rates:-

     

    All total =
    CALCULATE(
    SUM(Facts[Hourly rate]),
    ALL()
    )
     
    Then divide by 4 to get the quarters
     
    Quarters =
    DIVIDE('Dax measures'[All total] , 4 )
      
    Then you need to crate a driver table to "drive" your report
     
    QuartileQuartile ID
    Q11
    Q22
    Q33
    Q44
     
    Then create DAX measure to report your Quartiles
     
    Quartile rate =
    VAR Quarters = SELECTEDVALUE(Quartiles[Quartile ID])
    RETURN
    Quarters * [Quarters]
     
    I am an unpaid Power Bi volunter. Please click the thumbs uo if you like me trying to help you. Also click solved if this fixes your problem. One problem per ticket please. If you need to expand or change your problem them please click solved on this one and raise a new ticket. Thank you. 
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi speedramps 

       

      Thanks for taking the time this solution, but it is not quite what I am looking for as this is a measure, and what I am looking for is adding a column, i.e. it doesn't say how many lines do I have per quartile. What I am looking for is a way to categorize my employees into four categories, q1, q2, q3, q4, adding that column to my dataset. 

      Thanks for your help! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Anonymous,

        You can try to use the following measure formula if helps:

        Quartile =
VAR currRate =
    MAX ( Table[Hourly rate] )
VAR _min =
    MINX ( ALLSELECTED ( Table[Hourly rate] ), [Rate] )
VAR _max =
    MAXX ( ALLSELECTED ( Table[Hourly rate] ), [Rate] )
VAR interval =
    DIVIDE ( _max - _min, 4 )
VAR result =
    DIVIDE ( currRate - _min, interval )
RETURN
    IF ( result > INT ( result ), INT ( result ) + 1, result )

        Regards,

        Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Create a New column with the following formula:

    Quartile =
    IF (
    ISBLANK([HOURLY RATE]),
    BLANK(),
    VAR Rate = [HOURLY RATE]
    VAR Q1 = PERCENTILE.INC([HOURLY RATE], 0.25)
    VAR Q2 = PERCENTILE.INC([HOURLY RATE], 0.5)
    VAR Q3 = PERCENTILE.INC([HOURLY RATE], 0.75)
    RETURN
    SWITCH (
    TRUE(),
    Rate <= Q1, "Q1",
    Rate <= Q2, "Q2",
    Rate <= Q3, "Q3",
    "Q4"
    )
    )