Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
Thank-you! This has been so helpful!!!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |