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])),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
NamePerson Hours
Anthony8
Anthony12
Anthony8
Cassie16

 

Report2

NameWorking Hours
Anthony40
Cassie16

 

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

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.

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super 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.

  • ToddChitt's avatar
    ToddChitt
    Super 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.

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    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.