Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Help - Dividing

I need to create a measure called Utilization Rate that shows the sum of person hours divided by working hours with the output per person.   Utilization Rate = divide((sum('Report1'[PersonHours]))...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    Hello Anonymous,

     

    You can use the AVERAGEX function to iterate over each person in the Report1 table and divide their total person hours by their respective working hours from Report2.

     

    Utilization Rate = 
AVERAGEX(
    VALUES(Report1[Name]), 
    DIVIDE(
        SUM(Report1[Person Hours]), 
        LOOKUPVALUE(Report2[Working Hours], Report2[Name], Report1[Name])
    )
)

     

    Using this formula, the utilization rate for Anthony would be (8+12+8)/40 = 0.7, and the utilization rate for Cassie would be 16/16 = 1, which matches your desired results.

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

Hello Anonymous,

 

You can use the AVERAGEX function to iterate over each person in the Report1 table and divide their total person hours by their respective working hours from Report2.

 

Utilization Rate = 
AVERAGEX(
    VALUES(Report1[Name]), 
    DIVIDE(
        SUM(Report1[Person Hours]), 
        LOOKUPVALUE(Report2[Working Hours], Report2[Name], Report1[Name])
    )
)

 

Using this formula, the utilization rate for Anthony would be (8+12+8)/40 = 0.7, and the utilization rate for Cassie would be 16/16 = 1, which matches your desired results.