Because measures can exist in any table, it is often considered good practice to place measures in their own table for ease of reference and use.

To create this table, follow these steps:

1. Select the Home tab in the ribbon. Then, in the External data section, choose Get Data and then Blank Query. This opens the Power Query Editor.
2. Rename the query #Measures in the Query Settings pane by editing the Name property. Right-click the #Measures query in the Queries pane and uncheck Include in report refresh as there is no reason to refresh a blank query.
3. From the ribbon, choose Close & Apply. Note that a new table, #Measures, appears at the top of the tables list in the FIELDS pane.
4. Select Measure in the Hours table by clicking on the name Measure (not the checkbox).
5. From the Modeling tab of the ribbon, locate the Home Table field in the PROPERTIES section of the ribbon. Then, from the drop-down menu, choose #Measures. Measure disappears from the Hours table and now appears in the #Measures table, along with a column called #Measures.

6. Right-click the #Measures column (not the table), choose Delete, and confirm the deletion of the column. Right-click Measure and Rename this measure to #TotalBillableHours.

The #TotalBillableHours measure contains the calculation that we need for the numerator in our utilization calculation. Now, we need to create a measure for the denominator of our utilization calculation, that is, the total potential billable hours available within a period. We know that we cannot simply use the total hours reported, though, and that we must calculate the number of potential billable hours for any period of time in a different way.

To do this, we must have an independent way of calculating the number of hours within any arbitrary period of days. Follow these steps to do so:

1. Right-click the Calendar table and choose New column. In the formula bar, enter the following formula:

WorkHours = IF([IsWorkDay],8,0)

Recall that we previously created a calculated column called IsWorkDay that returned 1 for Monday, Tuesday, Wednesday, Thursday, and Friday and 0 for Saturday and Sunday. Hence, this calculated column uses row context to return either 8 or 0 for each row in the Calendar table. 

2. Right-click the #Measures table, choose New measure, and enter the following formula:

#TotalHours = SUM('Calendar'[WorkHours])

As we mentioned in the Understanding context for measures section, any reference to a column within a measure formula must be done within the explicit context of an aggregating function. In this case, we use the SUM function since we want the total potential number of billable hours within a period of days.

3. Right-click the #Measures table again, select New measure, and enter the following formula:

%Utilization = DIVIDE([#TotalBillableHours],[#TotalHours],0)

Note that, when referencing other measures within a measure formula, we don't need to use any kind of aggregation function because measures, by definition, must include an aggregation. 

4. Back on the Report view, uncheck any selected values in the Month slicer.
5. Select the Table visualization and add the #TotalHours and %Utilization measures to the table. Observe that the Table visualization exhibits some strange behavior. Many additional rows are displayed in the table; #TotalHours for every row is very large and the same for each row, that is, 6256. Considering that there are generally only about 2080 working hours in a year, something is definitely wrong.
6. Select the %Utilization measure in the FIELDS pane (not the checkmark).
7. From the Modeling tab in the ribbon, select the % button from the Formatting area to format the %Utilization measure as a percentage.

The issue here comes back to the filter context for our #TotalHours measure. Our measure formula only takes into account the WorkHours column in our Calendar table. However, because the relationship between our Calendar table and our Hours table is unidirectional from Calendar to Hours, the Calendar table is not being filtered based on the employee. Hence, all of the rows within the Calendar table are being included in the calculation for #TotalHours and hence, we are returning a result even when the employee is a past employee that has no reported hours within our Hours table. In addition, even for employees that do have reported hours within the Hours table, all of the rows within the Calendar table are being included versus just those dates that are being included in our Hours table for each employee.

Luckily, there is a simple fix for this. Follow these steps:

1. From the Modeling tab of the ribbon, choose Manage Relationships in the Relationships section. Ensure that the Hours (Date) to Calendar (Date) relationship is selected by clicking anywhere on that relationship row except the Active checkbox.
2. Choose the Edit button. Locate the Cross filter direction in the Edit relationship dialog and change this from Single to Both. Finally, click the OK button and then the Close button.

The Table visualization will now refresh and any past employees will disappear from the visualization. Our #TotalHours measure now agrees with our Average of TotalHours column. By changing the Cross filter direction to Both in our relationship, we have added filter context to the calculation of our #TotalHours measure so that, within the context of our table visual, the Calendar table becomes filtered based on the rows in the Hours table for each employee.

Looking more closely at our table visualization, we can see that the Total line for the table has a massive number for utilization, 2,389.55%. Obviously, this is not correct—well, yes and no. The calculation is correct but not what is expected or desired. Note that the Total for #TotalBillableHours is 149,490.15 and that the Total for #TotalHours is 6,256. Dividing these two numbers indeed returns 2,389.55%.

However, what we would expect is that the number for #TotalBillableHours and #TotalHours to simply be the sum of all of the numbers in our table. Unfortunately, the Total lines in the table and matrix visualizations do not work that way. The problem, again, is the filter context. The Total line in any table or matrix visualization is always evaluated in the context of ALL and this results in what is commonly called the measure totals problem. In the case of the #TotalHours measure, the ALL context simply returns the sum of the WorkHours from all of the rows from our Calendar table, resulting in 6256. However, we actually need this number to be the sum of all of the available work hours iterating over the available work hours of every employee.

Luckily, there is a standard technique for solving the measure totals problem. To implement this technique, follow these steps:

1. First, copy the formula for #TotalHours by selecting #TotalHours, clicking in the formula bar, and using Ctrl+A and Ctrl+C to copy the code.
2. Create a new measure called #TotalHoursByEmployee, paste in the code using Ctrl+V, and then change the formula to the following:

#TotalHoursByEmployee = SUM('Calendar'[WorkHours])

3. Edit the #TotalHours measure to the following formula:

#TotalHours = 
IF(
   HASONEVALUE(People[Name]),
    [#TotalHoursByEmployee],
    SUMX(
        SUMMARIZE(
            'People',
            People[Name],
            "__totalHoursByEmployee",
            [#TotalHoursByEmployee]
        ),
        [__totalHoursByEmployee]
    )
)

This DAX formula uses the HASONEVALUE function to determine whether the measure is evaluated within a single row in the table or a Total row. If there is one value for the People[Name] column, then we know that we are in an individual row within the table. If not, then we know that we are in a Total row since, in the context of ALL, there would be multiple values for the People[Name] column.

In this case, we essentially create a temporary calculated table within the DAX formula using SUMMARIZE, which emulates exactly how our information is displayed within our table visual. The SUMMARIZE function takes the name of a table, which in this case is People, the name of one or more columns within that table, which in this case is People[Name], and then allows for the creation of additional columns, in this case, __totalHoursByEmployee, with some kind of aggregation being performed, which in this case is simply a reference to our #TotalHoursByEmployee measure.

You can think of the SUMMARIZE function as a way to group rows within a table and apply aggregations to those groups of rows. This calculated table is used as input to the SUMX function. By using this, we simply sum the hours in our __totalHoursByEmployee column. 

We can now observe that our Total line appears to be correct, with a Total value for #TotalHours of 178,344 and a Total value for %Utilization of 83.82%. It should be noted that the #TotalBillableHours measure does not exhibit the measure totals problem. Hence, you may be curious about what determines whether a measure will exhibit this problem or not. Unfortunately, there is no straightforward answer to this question. The basic issue is that the measures in the total rows get evaluated in the context of all the rows in the current context. In other words, the measure is being aggregated for all of the rows in the current context for the total lines, not for each row individually in the current context and then be aggregated. Regardless, it is safe to assume that any measure could potentially exhibit the measure totals problem and so you should always be diligent in checking the Total line when dealing with measures in table and matrix visualizations.

Looking more closely at our table visualization, we can observe that %Utilization is blank for some employees. This occurs for employees that have no billable hours. We don't want %Utilization to be blank, but rather to display 0% in such cases. Again, there is an easy fix for this. In the FIELDS pane, select the %Utilization measure in the #Measure table by clicking anywhere on the measure name except the checkbox. The formula bar will be displayed and list the formula for the measure.

Edit this formula to the following:

%Utilization = 
VAR __utilization = DIVIDE([#TotalBillableHours],[#TotalHours],0)
RETURN
    IF(
        ISBLANK(__utilization),
        0 ,
        __utilization
    )

Note the use of VAR and RETURN in this formula. VAR and RETURN are paired functions in DAX. This means that once VAR is used, there must be RETURN. VAR allows for the creation of temporary values or variables within a DAX formula. These variables can be referenced within the rest of the DAX formula. Hence, the preceding code takes our original calculation for utilization and uses VAR to assign the value that's calculated to a variable called __utilization. The RETURN statement then specifies that if the value of the __utilization variable is blank, then return 0; otherwise, simply return the value from __utilization. DAX formulas can contain multiple VAR statements.

This edit to our %Utilization measure solves the issue of having blanks shown for %Utilization for employee rows. Unfortunately, this has also reintroduced the issue of having past employees show up in our Table visualization. One final tweak to our %Utilization measure fixes this:

%Utilization = 
VAR __utilization = DIVIDE([#TotalBillableHours],[#TotalHours],0)
VAR __days = COUNTROWS('Hours')
RETURN
    IF(ISBLANK(__days),
        BLANK(),
        IF(
            ISBLANK(__utilization),
            0 ,
            __utilization
        )
    )

Our %Utilization measure now adds an additional variable, __days. The calculation for the __days variable simply calculates the number of rows in the Hours table. Hence, for employees with no rows in the Hours table, the value for __days is nothing (BLANK). The RETURN statement, therefore, checks whether __days is blank (ISBLANK) and if so, returns nothing (BLANK).