Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Resource Utilization per Week

Hello all,    I am trying to calculate the utilization rate of resources. The utilization is calculated by billable hours / capacity.  I have two tables, one of which consists of the resource name...
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi,

     

    are you sure you want merge the two tables? It will get you in trouble if you want sum the capasity, since the capasity is repeated for each workday.

     

    You can use this dax to create a calculated column summing the work hours into the capacity-table:

    Hours per week =
CALCULATE (
    SUM ( 'Work'[Hours] );
    FILTER (
        'Work';
        'Work'[rName] = Capacity[rName]
            && 'Work'[Date] >= Capacity[startOfWeek]
            && 'Work'[Date] <= Capacity[startOfWeek] + 6
    )
)

     

    Example report

sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

Hi,

 

are you sure you want merge the two tables? It will get you in trouble if you want sum the capasity, since the capasity is repeated for each workday.

 

You can use this dax to create a calculated column summing the work hours into the capacity-table:

Hours per week =
CALCULATE (
    SUM ( 'Work'[Hours] );
    FILTER (
        'Work';
        'Work'[rName] = Capacity[rName]
            && 'Work'[Date] >= Capacity[startOfWeek]
            && 'Work'[Date] <= Capacity[startOfWeek] + 6
    )
)

 

Example report

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you very much for your answer!

 

I ended up modifying the solution a little bit to fit my needs, but I used your dax calculation as a base for my solution.