Forum Discussion

dgkallan's avatar
dgkallan
Helper 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
    )
amustafa's avatar
amustafa
Solution Sage
2 years ago

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's avatar
dgkallan
Helper II
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
)