Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Formula Help - Filter by Another Column

I am trying to write the following formula with the following tables

Numerator should be the (Sum of Total Person Hours) - (Sum of Total Person Hours where Hour Type equals Non-Working)

Denominator should be the (Sum of Total Working Hours).

 

Using this example, the formula should be (40-24) / (40) = 0.4

 

How can I write this formula as a column or measure?

 

Table: Time Logged

Resource NamePerson HoursHour Type
Jill8Client
Jill8Internal
Jill24Non-Working

 

Table: Resources

Resource NameWorking Hours
Jill40
Need Help

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

    try to add a column in the Resource table:

     

     

    column = 
VAR _nonworkinghours= 
SUMX(
    FILTER(
        TimeLogged,
        TimeLogged[Resource Name] = [Resource Name]
            &&TimeLogged[Hour Type] = "Non-Working"
    ),
    TimeLogged[Person Hours]
)
RETURN
DIVIDE( [Working Hours] - _nonworkinghours, [Working Hours])

     

    it worked like:

     

     

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    or you can plot the Resource Name from Resource table with a measure like:

    Measure = 
VAR _nonworkinghours= 
SUMX(
    FILTER(
        TimeLogged,
        TimeLogged[Resource Name] = MAX([Resource Name])
            &&TimeLogged[Hour Type] = "Non-Working"
    ),
    TimeLogged[Person Hours]
)
RETURN
DIVIDE( MAX([Working Hours]) - _nonworkinghours, MAX([Working Hours]))

     

    it worked like: