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
    )
amustafa's avatar
amustafa
Icon for Solution Sage rankSolution 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
    Icon for Helper II rankHelper 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
    )
  • dgkallan's avatar
    dgkallan
    Icon for Helper II rankHelper II
    2 years ago

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

      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:
      EmployeeDifferentialMaxMin
      A.881.040.00
      B1.041.040.00
      C1.041.040.00
      D1.041.040.00
      E.991.040.00
      F.991.040.00
      G.991.040.00
      H1.041.040.00
      I.971.040.00
          
      Any ideas?  This is very strange that one would work and not the other.