Hourly Rate Differential

[dgkallan]

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.  

[Daniel29195]

@dgkallan 

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. 

[amustafa]

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])

    )

)

[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:

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

Any ideas?  This is very strange that one would work and not the other.

[dgkallan · Accepted solution]

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

)