Median Salary Range and Quartiles

[MARIAGAJDUS]

Hi, 

I am quite a newbie and I am trying to calculate compa-ratio's of employee salaries, by level. This involves finding the median salary for each level and calculating where in the salary range each employee sits. 
The range I would like to use is:

Quartile 1 (The salary is more than 15% below median value)

Quartile 2 (5%-15% below median value)

Midpoint (Median value (+/- 5%))

Quartile 3 (5%-15% above median value)

Quartile 4 (More than 15% below median value)

The data set looks like:

ID      Salary in GBP             Level

1           100000                    Level 55

2           16578                         Level 15

3           13757                       Level 20

Thanks so much in advance!!

Maria

[v-alq-msft]

Hi, @MARIAGAJDUS 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column as below.

Quartile = 
var _med = 
MEDIANX(
    FILTER(
       'Table',
       [Level]=EARLIER('Table'[Level])
    ),
    VALUE([Salary in GBP])
)
return
SWITCH(
    TRUE(),
    [Salary in GBP]<0.85*_med,"Quartile 1",
    [Salary in GBP]>=0.85*_med&&[Salary in GBP]<0.95*_med,"Quartile 2",
    [Salary in GBP]>=0.95*_med&&[Salary in GBP]<1.05*_med,"Midpoint",
    [Salary in GBP]>=1.05*_med&&[Salary in GBP]<1.15*_med,"Quartile 3",
    [Salary in GBP]>=1.15*_med,"Quartile 4"
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MARIAGAJDUS]

Thanks Allan! This worked like a charm once I convered my salary column into a number. 

I tried to add another variable "country" into the MedianX function so that the calculation considers both the level and the country of the ID to produce the average salary, then catergorise them into quartiles.  Could you help with this?

Thanks, Maria

[v-alq-msft · Accepted solution]

Hi, @MARIAGAJDUS 

You may try the following measure.

Quartile = 
var _med = 
MEDIANX(
    FILTER(
       'Table',
       [Level]=EARLIER('Table'[Level])&&
       [Country]=EARLIER('Table'[Country])
    ),
    VALUE([Salary in GBP])
)
return
SWITCH(
    TRUE(),
    [Salary in GBP]<0.85*_med,"Quartile 1",
    [Salary in GBP]>=0.85*_med&&[Salary in GBP]<0.95*_med,"Quartile 2",
    [Salary in GBP]>=0.95*_med&&[Salary in GBP]<1.05*_med,"Midpoint",
    [Salary in GBP]>=1.05*_med&&[Salary in GBP]<1.15*_med,"Quartile 3",
    [Salary in GBP]>=1.15*_med,"Quartile 4"
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MARIAGAJDUS]

Thank-you! This has been so helpful!!!

[amitchandak]

@MARIAGAJDUS , Not very clear. PERCENTILEX.INC should able to help you

refer if these blogs can help

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156