Creating calculations
We now have a workable data model that contains all of the raw data we will need to report on utilization. Recall that, at the beginning of Chapter 4, Connecting to and
Transforming Data, with the rollout of unlimited Paid Time Off (PTO), it is imperative that employee utilization be tracked closely.
Utilization in this context is a calculation that involves the ratio of billable time versus the total time (billable and non-billable). From experience, the organization knows that, in order to remain profitable, the target utilization must be 80%. Therefore, we must create this calculation.
Calculated columns
Calculated columns are additional columns that are created in data model tables through the use of the DAX formula language. Calculated columns are computed for each row
in a table at the time of their creation, as well as during the refresh process. Hence, the data refresh process executes the queries defined in Power Query Editor. These queries create corresponding tables and/or refresh the data in the data model. Then, Power BI recalculates any DAX calculated columns for each row in the table.
This means that calculated columns are always up to date, based on the latest data that’s loaded into the data model. This also means that updates to the calculations only occur as part of the data refresh process. In other words, user interactivity, such as interacting with slicers or the application of filters, does not change the calculation of a calculated column for a row.
Understanding the context for calculated columns
Follow these steps to create a calculated column:
- Click on the Data view in the Views bar.
- Click on the Hours table in the Fields pane. From the ribbon, choose the Table tools tab and then New column in the Calculations section. The formula bar is populated by Column =, and a new field called Column appears in the Fields pane. Click the formula bar and enter the following formula:
Column = SUM([Hours])
A quick look at the results shows us that something is clearly not correct. The same number appears for every row in the table! We can see that this is the case by clicking on the drop-down arrow in the header for Column. Note that only a single value appears in the filter area—that is, 177997.15, as shown in the following screenshot:
Figure 5.6 – Column filtering dialog
To understand what is going on here, it is important to understand the concept of evaluation context. Evaluation context is the context in which a DAX formula evaluates a calculation.
There are two types of evaluation contexts in DAX: row and filter contexts. Calculated columns, by default, evaluate within what is called row context. Row context means that when a DAX calculation evaluates, it only considers the current row, and nothing else.
However, certain functions, such as SUM, override this default context. In this case, the
SUM function is overriding the row context of the calculated column and imposing
a filter context of the entire table. Hence, the output for each of the rows is the sum of all of the hours in the entire table. All of the standard aggregation functions—that is, SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT, and so on—exhibit this behavior.
In order to see row context in action, create another new calculated column and enter the following formula:
Column 2 = [Hours]/[TotalHours]
In this calculation, the TotalHours column contains the total number of hours that were reported during the entire reporting period specified by the PeriodStartDate and PeriodEndDate columns. Because no functions have been used that change
the evaluation context from row to filter, only the current row is considered during the evaluation of the formula.
Hence, this formula simply divides the Hours column in each row by the TotalHours column in each row. Now, if we click on the drop-down arrow for Column 2, we will see that there are many different values.
Creating calculated columns for utilization
For the purposes of calculating utilization, we want the total hours billed by an employee divided by the total hours reported by an employee.
In order to get the first part of this—that is, the total hours billed—we need to create a third calculated column using the following formula:
Column 3 =
SUMX(
FILTER(
ALL(‘Hours’), [Category] = “Billable” && [EmployeeID] =
EARLIER([EmployeeID])
),
[Hours]
)
Use Shift + Enter and the Tab key to create the correct formatting. Shift + Enter inserts a new line or break, while Tab indents text that’s entered.
By way of explanation, the SUMX function takes two parameters: a table and a numeric column. These parameters are separated by a comma (,). The SUMX function iterates over the specified table and sums the specified column. Hence, in the preceding formula, the first parameter is the FILTER function (line 3), while the second parameter is simply the Hours column (line 7).
The FILTER function itself takes two parameters. The first parameter is the ‘Hours’ table, on line 4, and a filter, on line 5. The ALL function returns an entire table stripped of all of its row and filter contexts. Although technically not required in this context, the
ALL function ensures that we are operating against all of the rows in the table. The second parameter is our applied filter. This filter actually contains two parts joined by logical
AND criteria, &&. In DAX, && is a logical AND operator, while || is a logical OR operator. Hence, the first part ensures that the table that’s returned by the FILTER function only contains rows that have a Category value of Billable. The second part ensures that the table that’s returned by the FILTER function also only contains rows for the employee in the current row. To do this, this part of the filter uses the EARLIER function.
The EARLIER function is perhaps the worst named function in all of DAX. The logic behind the naming of this function makes some amount of sense if you are intimately familiar with the DAX evaluation context but generally makes no sense at all to the common layman. You should think of EARLIER as being the current row value for the column specified. Hence, I like to think of EARLIER as the current value. Another construct that can do the same thing is the use of variables or VAR statements. We will find out more about using DAX variables a little later.
To understand what’s going on here, we know that the default context of a calculated column is the row’s context. However, because we are using functions such as SUMX and ALL, we have overridden this default row context and imposed a filter context. Hence, to ensure that the table that’s returned only includes rows for the employee in our current row, we need to revert to the earlier evaluation context—that is, the row context. This is the purpose of the EARLIER function. Within the EARLIER function, the row context is reestablished, and hence EARLIER([EmployeeID]) returns the value of EmployeeID in the current row.
Therefore, the Column 3 formula can be read in the following way:
- Take the entire table (ALL) and filter this table down so that it only has rows where the Category column has a value of Billable and rows where the EmployeeID column matches the current row value for EmployeeID.
-
Second, sum the Hours column for this table and return the result. Each row for an employee should return the exact same numeric value, which is the total hours that were reported by that employee that were billable hours.
-
- For the second part—that is, the total number of hours reported by an employee— we simply need to create another new column with a slight modification to remove the filter for only billable hours. This column can be written with the following formula:
Column 4 =
SUMX(
FILTER(
ALL(‘Hours’),
[EmployeeID] = EARLIER([EmployeeID])
),
[Hours]
)
- Finally, to create our utilization calculation, create a new calculated column that utilizes row context, as follows:
Column 5 = DIVIDE([Column 3], [Column 4],0)
While the preceding formula could have been written as Column 5 = [Column 3] / [Column 4], it is a good idea to use the DIVIDE function instead of the divide (/) operator. The DIVIDE function takes a third parameter, which allows for the return of an alternate number instead of an error in the event of an attempt to divide by zero.
We can now select Column 5 by clicking on its header and change its formatting to be a percentage. To change the column to a percentage, we need to go to the Formatting section of the Column tools tab in the ribbon. From here, select the % icon.
Now that we have the desired calculation for utilization in Column 5, we can clean up some of our previously created columns. To perform this cleanup, execute the following steps:
- Right-click the header for Column and choose Delete.
- Confirm the deletion of this column in the Delete column dialog box by clicking Delete.
- Repeat this procedure for Column 2.
-
Double-click the TotalHours column and rename this column
TotalHoursInPeriod.
- Rename Column 3 TotalBillableHours, Column 4 TotalHours, and
Column 5 % Utilization.
- Rename Column 3 TotalBillableHours, Column 4 TotalHours, and
We are now ready to use these new calculated columns in visualizations. To use these columns in visuals, do the following:
- Click on the Report view in the Views bar.
- Shrink the existing matrix visualization horizontally to create space on the report canvas.
- Click on an empty portion of the report canvas to deselect the existing table.
- Click Name from the People table to create a new table visualization on the blank area of the report canvas.
- With this new table selected, click on TotalBillableHours, TotalHours, and % Utilization from the Hours table to add these to this second table visualization.
Note that the values that are returned in this second table are really large! This is because the default aggregation for numeric fields is Sum. Since all of the rows for each employee contain our desired number, summing these values doesn’t make sense. To correct this, do the following:
- In the Values field well of the Visualizations pane, select the drop-down arrow next to TotalBillableHours and choose Average instead of Sum as the aggregation.
- Repeat this procedure for TotalHours and % Utilization.
Continuing with the scenario we introduced in Chapter 2, Planning Projects with Power BI, Pam is initially happy with the utilization calculation that she has created and decides to share her progress with her colleague, Mike, who is also in the finance department. Mike is impressed by the work so far, but as they start to talk through the utilization calculation, they start to identify some potential problems.
First, the utilization calculation is only taking into account the hours reported, not the potential billable hours. For example, if a user only reports 30 total hours within a
particular week when there are really 40 potential billable hours for that week, then the current utilization calculation will be incorrect. Furthermore, managers will likely want to view utilization by particular weeks, months, or a range of dates.
The current utilization calculation is essentially static and based on all of the data within the Hours table. Since calculated columns are only calculated at the time of their creation and during data refresh, it is not possible to have the data refresh be dynamically based on user interaction with the report.
Clearly, calculated columns are not the way to go. Pam needs to rethink how the utilization calculation occurs within Power BI.
Using calculated columns to resolve data granularity issues
While calculated columns may not be a fit for Pam’s utilization calculation, calculated columns are useful in other ways. Recall that we did not create a relationship for the Budgets and Forecasts table. The main issue here is that the Budgets and Forecasts table has a data granularity of month while our Hours and Calendar tables have a data granularity of day. However, we can resolve this data granularity issue through the use of calculated columns as well as a calculated table.
To resolve the data granularity issue, start by creating the following calculated column in the Budgets and Forecasts table:
MonthYear = [Month] & “2017”
Here, we use DAX’s & concatenation operator to concatenate each row’s Month column value with the text value 2017 since the Budgets and Forecasts data is for the year 2017.
Similarly, we can create a calculated column in the Calendar table using the following formula:
MonthYear = LEFT([Month], 3) & [Year]
The only difference in this formula is that we have used the LEFT function to get the first three letters of the Month column since the Calendar table uses the full name of the month versus an abbreviation, as with the Budgets and Forecasts table.
We can now use the MonthYear columns in the Budgets and Forecasts table and the Calendar table to create a relationship between the tables. However, if we do that, we receive a warning when creating the relationship:
This relationship has cardinality Many-Many. This should only be used if it is expected that neither column (MonthYear and MonthYear) contains unique values, and that the significantly different behavior of Many-many relationships is understood.
Since the Budgets and Forecasts table contains a budget for each city for each month, there are multiple rows with duplicate MonthYear values. The same is true for the Calendar table. While relationships with Many-Many cardinality are allowed in Power BI since the July 2018 release of Power BI Desktop, they are often best avoided. To resolve the Many-Many relationship issue, perform the following steps:
- Click Cancel to not create the relationship or otherwise delete the Many-Many relationship between the Budgets and Forecast table and Calendar table if already created.
-
While in the Data view, choose the Table tools tab in the ribbon and then select
New table from the Calculations section.
-
Enter the following formula:
MonthYears = DISTINCT(‘Calendar'[MonthYear])
- Switch to the Model view and create a relationship between the MonthYear column in the MonthYears table and the MonthYear column in the Calendar table.
- Create a relationship between the MonthYear column in the MonthYears table and the MonthYear column in the Budgets and Forecasts table.
Note that both of the relationships have a Cardinality value of Many-to-one (*:1) with the
MonthYears table on the 1 side of the relationships.
Measures
As noted previously, Pam needs to rethink her utilization calculation because calculated columns are static once data is loaded or refreshed. Luckily, there are DAX calculations that can be dynamically calculated depending upon user interaction. These calculations are called measures. Measures can be either implicit or explicit. Implicit measures are created automatically by Power BI whenever a numeric field is used within a visualization and are sometimes created for text fields as well, within certain visualizations in
certain circumstances. These implicit measures include the standard aggregations— that is, sum, average, maximum, minimum, distinct count, count, standard deviation, variance, and median for numeric fields, and first, last, distinct count, and count for text fields. We observed implicit measures when we created our table visualizations in the previous section, Calculated columns. When we placed the TotalBillableHours,
TotalHours, and % Utilization columns in our table, the values were calculated via an implicit Sum measure that we changed to Average.
However, measures can also be explicitly defined by a user to have very specific, and potentially quite complex, calculations. As with calculated columns, writing explicit measures involves DAX code. However, unlike calculated columns, measures are dynamic in their calculation so that measures are calculated just in time, every time there is an interaction with the end user. Measures are not recalculated during data refresh but rather every time a user views or interacts with a report. This makes measures truly dynamic, and hence they are perfect for a scenario where end users may change the evaluation context on the fly.
Understanding the context for measures
To demonstrate how measures are different compared to calculated columns, we will execute the following steps:
- In the Report view, create a new page by right-clicking the Utilization page tab and selecting Duplicate Page. A new page called Duplicate of Utilization is created.
- Remove the matrix visualization on the left by selecting the matrix and then clicking the Delete or Del key. This deletes the matrix visual from the canvas. Use the sizing handles to expand the remaining table visualization so that the table visual takes up the entire page.
- Right-click the Hours table in the Fields pane and choose New measure. The formula bar is activated. In the formula bar, enter the following formula:
Measure =
SUMX(
FILTER(
‘Hours’,
‘Hours'[Category] = “Billable”
),
‘Hours'[Hours]
)
Note that the formula for our Measure column is extremely similar to the formula for our TotalBillableHours column from the Creating calculated columns for utilization section. The main differences are that we prefix column references with the name of the table in which those columns exist and that we have removed our EmployeeId
filter clause.
While not strictly required in all circumstances, since measures can exist in any table, it is a good practice to refer to any column within a measure using both the table name and column name. If no table name is specified for a column, the measures assume the
current table, and this can cause problems when moving measures between tables when duplicate column names exist in multiple tables.
It is important to understand that, unlike calculated columns, measures have no implicit row context, only a filter context. This means that when measures refer to a column in
a table, that column must be referenced within an explicit aggregation function such as SUM, AVERAGE, MAX, MIN, and so on. This also means that while measures can be used within a calculated column formula, the row context for that calculated column simply becomes the initial filter context for the measure calculation.
We can now use this measure in our visualization by performing the following steps:
- Place this Measure into our table visualization on the Duplicate of Utilization page and note that the results displayed are exactly the same numbers that we have for the average of our calculated column, TotalBillableHours.
- Shrink this table visualization horizontally.
-
Click on a blank area on the canvas and then select the Slicer visualization from the
Visualizations pane.
- Place Month from our Calendar table into the Field well for our slicer.
- Select March from within the slicer.
By doing this, we can see how calculated columns and measures are different. We can observe that the value for the average of TotalBillableHours did not change, but the value for Measure is now much lower. If we select February or January in our slicer, again, the calculated column values do not change, while the values for our Measure do.
To understand what is happening, it is important to recognize that, while the slicer filters the rows in the Hours table for both the calculated column and the measure since the calculated column has the same value for every row per employee, the average of those values never changes.
With the measure, the summation calculation specified in the formula is performed within the combined context of the visualizations and the DAX formula to create a truly dynamic evaluation context.
This evaluation context actually comes from three different sources, outlined as follows:
- The first source is the table visualization itself. Because we have the Name value of the employee in our table, this creates a filter context where Measure only considers rows within the Hours table that are related to the Name value of the employee from the People table for each row in the table visualization.
- The second source is the Slicer visualization. By selecting a particular Month value in the slicer, this adds extra filter context where the measure also only considers rows within the Hours table that are related to the Month value that’s selected from our Calendar table in the slicer.
- Finally, our explicit definition of the measure adds a final filter context where the measure only considers rows in the Hours table that have a Category value of Billable.
Creating measures for utilization
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 such a table, follow these steps:
- Select the Home tab in the ribbon. Then, in the Data section, choose Get data and then Blank Query. This opens Power Query Editor.
- Rename the Calculations query in the Query Settings pane by editing the Name
property.
- Right-click the Calculations query in the Queries pane and uncheck Include in report refresh as there is no reason to refresh a blank query.
- From the ribbon, choose Close & Apply. Note that a new table, Calculations, appears at the top of the tables list in the Fields pane.
- Back in Power BI Desktop, in either the Report or the Data view, select Measure in the Hours table by clicking on the name Measure (not the checkbox).
-
From the Measure tools tab of the ribbon, locate the Home table field in the Structure section of the ribbon. Then, from the drop-down menu, choose Calculations. Measure disappears from the Hours table and now appears in the Calculations table, along with a column called Calculations.
Note the different icons displayed to the left of the measure, Measure, and the column, Calculations.
- Right-click the Calculations column (not the table), choose Delete from model, and confirm the deletion of the column. Note that the icon for the Calculations table changes and the Calculations table now appears at the top of the Fields pane.
- Right-click Measure in the Calculations table and rename this measure Total Billable Hours.
- Right-click the Calculations query in the Queries pane and uncheck Include in report refresh as there is no reason to refresh a blank query.
Because measures can exist in any table, the name of a measure cannot be the same as the name of any table, column, or any other measure within a data model. Thus, we cannot name our measure TotalBillableHours, as we did for our existing calculated column.
There are many different schemes for helping to ensure uniqueness. A common practice is to prefix the names of measures with something such as an underscore (_), m, or #, in order to help guarantee uniqueness and denote that this is a measure. Some users utilize multiple measure prefixes, such as # to denote numeric measures, $ to denote measures formatted as currency, % for percentages, and @ for text. Prefixing measure names with special characters also has the benefit of keeping these measures at the top of tables.
However, there are other schools of thought that specify that measure names should be intuitive to the end user, and this is the approach we use here.
The Total Billable Hours 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 and thus utilization within any arbitrary period of days. Follow these steps to do so:
- While in the Report or Data view, right-click the Calendar table in the Fields pane 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.
- Right-click the Calculations table, choose New measure, and enter the following formula in the formula bar:
Total Hours = SUM(‘Calendar'[WorkHours])
As we mentioned in the Understanding the 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.
- Right-click the Calculations table again, select New measure, and enter the following formula in the formula bar:
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.
- While in the Report view, uncheck any selected values in the Month slicer.
- Select the % Utilization measure in the Fields pane (not the checkmark box).
-
From the Measure tools tab in the ribbon, select the % button from the Formatting
section to format the % Utilization measure as a percentage.
- Select the table visualization and add the Total Hours and % Utilization measures to the table. Observe that the table visualization exhibits some strange behavior. Many additional rows are displayed in the table; Total Hours 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.
The issue here comes back to the filter context for our Total Hours 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 Total Hours, and hence, we are returning a result even when the employee is a past employee that has not 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:
- While in the Report view, select the Modeling tab of the ribbon and then 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.
- Choose the Edit button. Locate the Cross filter direction option 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 refreshes and any past employees disappear from the visualization. Our Total Hours measure now agrees with our Average of TotalHours column.
By changing the Cross filter direction setting to Both in our relationship, we have added row context to the calculation of our Total Hours 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: 2389.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 value for Total Billable Hours is 149,490.15 and that the Total value for Total Hours is 6256. Dividing these two numbers does indeed return 2389.55%.
However, what we would expect is that the number for Total Billable Hours and Total Hours would 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 Total Hours measure, the ALL context simply returns the sum of the WorkHours column 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:
- Rename the Total Hours measure to Total Hours by Employee.
-
Create a new Total Hours measure using the following formula:
Total Hours =
IF(
HASONEVALUE(People[Name]),
[Total Hours by Employee],
SUMX(
SUMMARIZE(
‘People’, People[Name],
” Hours”, [Total Hours by Employee]
),
[ Hours]
)
)
- Since we renamed our original Total Hours measure to Total Hours by Employee, Power BI also modified the % Utilization measure to reflect this name change. Therefore, edit the % Utilization measure to again be the original formula, as follows:
- Add the new Total Hours measure to the table visualization.
This DAX formula for our new Total Hours measure 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 the case of a Total row, 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, Hours, with some kind of aggregation being performed, which in this case is simply a reference to our Total Hours by Employee 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
Hours column.
It is considered good practice to prefix things such as calculated tables, column names, and variables created within DAX formulas with something such as an underscore,
a double underscore, or other identifying character or characters. This assists with readability and can reduce confusion when referring to objects within DAX formulas.
We can now observe that our Total line in our table visualization now appears to be correct, with a value for Total Hours of 178344 and a total value for % Utilization of 83.82%. It should be noted that the Total Billable Hours measure does not exhibit the measure totals problem, while Total Hours by Employee does.
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 being 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. The easiest way to see this is to click twice on the % Utilization column header in the table visualization in order to sort the table in an ascending
fashion. This can also be done by selecting the table visualization and then clicking on the ellipses (…) in the upper-right or lower-right of the table visualization and choosing Sort ascending. Blank values for % Utilization occur for employees that have no billable
hours. We don’t want % Utilization to be blank, but rather to display 0.00% in such cases. Again, there is an easy fix for this. In the Fields pane, select the % Utilization measure in the Calculations table by clicking anywhere on the measure name except the checkbox.
The formula bar will be displayed and will list the formula for the measure.
Edit this formula to the following:
% Utilization =
VAR utilization =
DIVIDE([Total Billable Hours],[Total Hours],0)
RETURN
utilization + 0
Note the use of VAR and RETURN in this formula. VAR and RETURN are paired functions in DAX, which means that once VAR is used, there must be a RETURN statement as well. VAR allows 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 adds 0 to this calculation to ensure that 0 is returned instead of a blank value. DAX formulas can contain multiple VAR statements.
It’s good practice to use VAR statements in order to avoid repeated code as well as to aid in the readability of DAX formulas.
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, as illustrated in the following code snippet:
% Utilization =
VAR utilization =
DIVIDE([Total Billable Hours],[Total Hours],0)
VAR days = COUNTROWS(‘Hours’)
RETURN
IF(
ISBLANK( days),
BLANK(),
utilization + 0
)
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). Otherwise, the utilization variable is returned, plus 0.
Now that we have our calculations, we need to check our calculations and troubleshoot any issues we find.
