Forum Discussion

dgkallan's avatar
dgkallan
Icon for Helper II rankHelper II
2 years ago
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...
Need Help
  • dgkallan's avatar
    dgkallan
    2 years ago

    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
    )
Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    dgkallan
    Icon for Helper II rankHelper II
    2 years ago

    Thank you.  This did not give an expected average by job profile - I'm assuming it gave an average of all hourly salaries.