Forum Discussion

MARIAGAJDUS's avatar
MARIAGAJDUS
Frequent Visitor
6 years ago
Solved

Median Salary Range and Quartiles

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      MARIAGAJDUS
      Frequent Visitor
      6 years ago

      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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.

  • marap1988's avatar
    marap1988
    Frequent Visitor
    2 years ago

    Hi. I am new in PBI. I have the amount of salaries by grades (levels) in ecell and I need to report median salary for each grade and calculating where in the salary range each employee sits. Can you help me and give the instruction how to do that.

    Thanks

    Marija