DAX Help - Dividing

[Anonymous]

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])),SUM('Report2'[Working Hours])) gives me the company-wide number

Utilization Rate = divide((sum('Report1'[PersonHours])),MAX('Report2'[Working Hours])) divides everyone by 40 hours, but not everyone works 40 hours.

How do I write this if neither SUM nor MAX work?

Report1

Name	Person Hours
Anthony	8
Anthony	12
Anthony	8
Cassie	16

Report2

Name	Working Hours
Anthony	40
Cassie	16

For Anthony, this should appear as (8+12+8)/40 = .7

For Cassie, this should appear as (16)/16 = 1

When I use "SUM", Cassie appears as (16)/56 = .28

When I use "MAX", Cassie appears as (16)/40 = .4

Neither are correct

Thanks

[sevenhills]

I will do this way ... 

Util Rate = 
var _h =   sum (Table_Actuals[Person hours])  
var _wh = CALCULATE( sum(Table_Alloted[Working Hours]), 
Filter(Table_Alloted, Table_Alloted[Name] in  VALUES(Table_Actuals[Name])))

return  DIVIDE(_h, _wh ) 

Format the data type as decimal number.

[Sahir_Maharaj · Accepted solution]

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]

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.