Forum Discussion
Solved
Hourly 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...
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)
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?
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.
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 |