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.

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

Is there come relationship between these two reports? Ideally, they should both be joined to an Employee dimension on a one to many relationship. Then, break out the DIVIDE by asking yourself: What is the Numerator for this? Create a MEASURE for that. What is the Denominator? Create a MEASURE for that. Ideally the Numerator would probably be in Report 1 and the Denominator would be in Report 2. 

 

Add a table visual and add the person from the Employee dimension, and the two base measures or Numerator and Denominator. Make sure those two measure are reporting correct numbers.

 

Finally, create the DAX Measure in the Employee table using the DIVIDE functin, but using the Measures.