Forum Discussion

FIONAA's avatar
FIONAA
New Member
3 years ago
Solved

Calculation for Quartiles

I wonder if someone can help, I have a Staff List and I am trying to calculate the Quartile an employee sits in within the grade they are in based on their FTE Salary.

 

Below is the calculation I have used which I found in another post (https://community.powerbi.com/t5/Desktop/Median-Salary-Range-and-Quartiles/td-p/1402652) but when I filter on some of the Grades to check the split, I have no-one showing for some of the grades at Quartile 1 for example.  Some grades only have for example people in Midpoint, Quartile 2 and Quartile 4.

 

Is the formula I am using correct for what I am trying to do?

 

Quartile =
var _med =
MEDIANX(
FILTER(
'Sheet1',
[Grade]=EARLIER(Sheet1[Grade])
),
VALUE(Sheet1[FTE Salary])
)
return
SWITCH(
TRUE(),
[FTE Salary]<=0.85*_med,"Quartile 1",
[FTE Salary]>=0.85*_med&&[FTE Salary]<0.95*_med,"Quartile 2",
[FTE Salary]>=0.95*_med&&[FTE Salary]<1.05*_med,"Midpoint",
[FTE Salary]>=1.05*_med&&[FTE Salary]<1.15*_med,"Quartile 3",
[FTE Salary]>=1.15*_med,"Quartile 4"
)
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    It might be "correct" but why not use the built in Percentile functions?

     

    by the way it can be slightly simplified

     

    return
SWITCH(
TRUE(),
[FTE Salary]<=0.85*_med,"Quartile 1",
[FTE Salary]<0.95*_med,"Quartile 2",
[FTE Salary]<1.05*_med,"Midpoint",
[FTE Salary]<1.15*_med,"Quartile 3",
"Quartile 4"
)

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    It might be "correct" but why not use the built in Percentile functions?

     

    by the way it can be slightly simplified

     

    return
SWITCH(
TRUE(),
[FTE Salary]<=0.85*_med,"Quartile 1",
[FTE Salary]<0.95*_med,"Quartile 2",
[FTE Salary]<1.05*_med,"Midpoint",
[FTE Salary]<1.15*_med,"Quartile 3",
"Quartile 4"
)