The first measure, Total Billable Hours, calculates the total amount of billable or productive time. This is done by checking whether the PayType of the employee is equal to ADMINISTRATION. If so, then 0 is returned as the employee is not expected to be billable. Otherwise, the amount of billable time is computed by using FILTER to return only the rows in R01_Table that have a Category of Billable and then using SUMX to sum the Hours column.
The next measure, Total Hours, calculates the total amount of billable (productive) and non-billable time (unproductive). However, the PayType of each billable employee affects how Total Hours is calculated. We do this by using a SWITCH statement based upon the PayType column. Since we must use an aggregating function such as MAX when referencing columns within a measure, it is important to realize that this measure is only valid when it's used within the context of a single EmployeeID and that the employee must not have changed PayType.
For HOURLY and SUB-CONTRACTOR employees, since these employees are only paid for billable hours, we simply return the value from our Total Billable Hours measure for these employees. This means that HOURLY and SUB-CONTRACTOR employees are always considered to have 100% utilization. However, if the employee has a PayType of SALARY, then the sum of all potential work hours is returned from the R01_Calendar table.
Now that we have measures for productive time (Total Billable Hours) and all productive and unproductive time (Total Hours), we can calculate utilization in our % Utilization measure. This is fairly straightforward: we create a base table variable called __Table that uses SUMMARIZE to return a unique list of EmployeeID values that are currently in context. We can then use SUMX to sum our Total Billable Hours measure and store this value in the __TotalBillableHours variable. We can perform the same process to calculate __TotalHours. Then, we simply need to divide __TotalBillableHours by __TotalHours to get our utilization (__Utilization). In our RETURN statement, we simply check if __Utilization is blank (ISBLANK) and, if so, return 0. Otherwise, we return __Utilization.