Forum Discussion
Helper IIHourly Rate Differential
I have a table that generally looks like the below:
| Employee | Hourly Rate | Role | Race | Gender | Tenure years |
| A | $18.50 | ICU RN | White | Female | 1.25 |
| B | $17.00 | ICU RN | Black | Female | 2.5 |
| C | $16.50 | ICU RN | Asian | Male | 1.75 |
There are obviously more employees with different roles and a variety more races, however this is a sample.
Here's what I'm trying to accomplish. I first want to establish a salary differential within a job role, which is essentially comparing an hourly rate to the average within the Role. The average above would be $17.33, so Employee A would have a salary differential of 1.06 and Employee C would have a salary differential of .95.
I then want to be able to take that salary differential and use the Key Influencers visual to be to look at race, gender, tenure, and a few other factors to see what influence those have on the salary differential.
I'm struggling with the approach. Does I start with some sort of measure, or a column, or a build a table of averages? For some reason, I just can't wrap my head around this one.
This worked, except the SUM needed to be removed from the second column.
Average Hourly Rate by Role =CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table', 'Table'[Role]))Job Profile Hourly Differential =DIVIDE('Table'[Hourly Rate],CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table','Table'[Role])),0)
6 Replies
- amustafa
Solution Sage
To start with, create two measures in your table and see if that helps. You need more than 3 columns to analyse key influencers.
Average Hourly Rate by Role =CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table', 'Table'[Role]))Salary Differential =DIVIDE(SUM('Table'[Hourly Rate]),CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table', 'Table'[Role])))
dgkallanThis worked, except the SUM needed to be removed from the second column.
Average Hourly Rate by Role =CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table', 'Table'[Role]))Job Profile Hourly Differential =DIVIDE('Table'[Hourly Rate],CALCULATE(AVERAGE('Table'[Hourly Rate]),ALLEXCEPT('Table','Table'[Role])),0)- dgkallan
Give a pig a pancake...:)
So, I have the average within the role and a differential. How would you then calculate the spread in the differential to figure out which roles have the most variance?
- dgkallan
I tried using
CALCULATE(MAX(dimWorker[Job Profile Hourly Differential]),ALLEXCEPT(dimWorker,dimJobFamily[Job Profile]))-CALCULATE(MIN(dimWorker[Job Profile Hourly Differential]),ALLEXCEPT(dimWorker,dimJobFamily[Job Profile]))I got only the Max result so I split into two columns using the same DAX above. As you can see, the Max works, but the Min returns a 0. Very strange....any ideas?
MAX Result:
Any ideas? This is very strange that one would work and not the other.Employee Differential Max Min A .88 1.04 0.00 B 1.04 1.04 0.00 C 1.04 1.04 0.00 D 1.04 1.04 0.00 E .99 1.04 0.00 F .99 1.04 0.00 G .99 1.04 0.00 H 1.04 1.04 0.00 I .97 1.04 0.00
- Daniel29195
Community Champion
to calculate the average in this table you can use this measure :
option1 :
average =
calculate (average(table_name[col_num] ) ,
all(table_name) ,
values( table_name[role_name])
)
option2 :
average =
calculate (average(table_name[col_num] ) ,
allselected(table_name),
values( table_name[role_name])
)
the difference between the 2 options, is that option1 will not take into consiedartion slicers and filters ,
however option2 will caluclate your measure taking into consideration the slicers and filters you apply.
having the average, i guess you can now do the logic you want base on the business logic.
If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that thumbs up button 🫡👍
- dgkallan
Thank you. This did not give an expected average by job profile - I'm assuming it gave an average of all hourly salaries.
