Understanding Calculated Columns and Measures
On initial interaction with Power BI, many people might get confused about when to use Calculated columns vs Measures. In this blog, we will look at the differences between DAX Calculated Columns and Measures. DAX (Data Analysis Expressions) is a formula language at the heart of Power BI. The two ways in which you can use DAX is by creating Calculated Columns and Measures. Calculated columns create new data in tables themselves and are NOT great for aggregation such as COUNT or SUM.
CALCULATED COLUMNS and MEASURES Examples:
In this blog, we will use two examples to illustrate the difference between Calculated Columns and Measures. These two examples are 1. Age and 2. Profit and Profit Margin.
1. AGE:
Age is a good example of a variable that does not change based on filter context, it evaluates row by row for every customer. Suppose you want to do customer profiling, where your data needs to be filtered by customer age to see the revenue spent per age group. Furthermore, your data set contains a column for the client’s date of birth and not their age.
To illustrate why a calculated column would be best in this instance, let’s start by creating a Calculated column from the “date of birth” column:
1.1. Start by duplicating the Client Date of Birth Column: Navigate to the Add Column ribbon, then click on Duplicate Column:
1.2. Rename the duplicated Date of Birth Column to Client Age Column, then from the Transform ribbon, navigate to Date, and select Age:
1.3. Because Age will give us the age in the number of days instead of the number of years, you need to navigate to Duration from the Transform ribbon and click on Total Years:
1.4. Total Years is adding decimal points, therefore you will have to click on Round Down in the “Rounding” drop-down menu situated under the Transform ribbon:
1.5. Now that you have a column for age, you can see the revenue per age group by using a visual such as a slicer and selecting the various ages:
In conclusion, we can see that by creating the calculated column, we are now able to filter customers by age. If you had calculated the age using a calculated measure instead, you would not be able to filter the report by age brackets.
To further demonstrate this point, let’s use a second example:
2. PROFIT AND PROFIT MARGIN:
| Sample data Model | |||||
|---|---|---|---|---|---|
| Product Column | UOM Column | Cost Column | Price Column | Quantity | Total Revenue Column |
| T-Shirt | Ea | 10.00 | 15.00 | 1 | 15.00 |
| Leggings | Ea | 5.00 | 10.00 | 2 | 20.00 |
| Gloves | Pair | 5.00 | 8.00 | 1 | 8.00 |
| Shoes | Pair | 15.00 | 30.00 | 3 | 90.00 |
| Shoes | Pair | 15.00 | 20.00 | 2 | 40.00 |
| Leggings | ea | 5.00 | 10.00 | 1 | 10.00 |
| T-Shirt | Ea | 10.00 | 15.00 | 2 | 30.00 |
We are interested in computing:
A. Profit (Total Revenue – Cost)
B. Profit Margin [(Total Revenue-Cost)/(Total Revenue)]
2.1. We want to add a Calculated column for Profit. So we must navigate to the home tab and click on New column:
Name your New Column: Profit Column, where:
Profit Column = Data[Total Revenue Column]-Data[Cost Column]
2.2. Thereafter we need to add a Calculated column for Profit Margin. An alternative method to add a column is to navigate to the data field and click on the ellipsis, then select New column:
Name your New Column: Profit Margin % Column, where:
Profit Margin % Column = DIVIDE(Data[Profit Column],Data[Total Revenue Column])
The Output as given by a Matrix visual is as follows:
The profit Margin % Column per row i.e. Gloves, Leggings, etc. is correct, however, when we look at the Total, we can immediately see an error:
$148/$213= 69.48% and not 408.33%,
This is because the Calculated Column for Profit Margin (%) is aggregated to get to total, arithmetic average, and not weighted average.
2.3. Therefore, a Measure for Profit Margin would be better, so let’s create a Measure by navigating to the home tab, and clicking on New Measure:
We already have Calculated columns for Profit and Revenue, therefore we can do the following when creating our Profit Margin % Measure:
Profit Margin % Measure = DIVIDE(sum(Data[Profit Column])/sum(Data[Total Revenue Column])
The new output as indicated by the Matrix visual is:
From the above example;
It is evident that, when calculating something like Profit: (Total Revenue – Cost) you can use either a Calculated Column or a Measure, however, when you want to calculate Profit Margin ((Total Revenue-Cost)/Total Revenue), it would be best to use a Measure. Calculated Columns are great for something more static like dates or ages, which won’t change due to dicing and slicing.
For a better understanding of Calculated Columns and Measures in DAX, it might be helpful to compare their properties:
A tabular summary of Calculated Columns VS. Measures:
| CALCULATED COLUMNS | MEASURES |
|---|---|
| Can return multiple results | Measures always only return one value. Therefore, you cannot filter by the results of a calculated measure i.e. cannot place a calculated measure on a slicer. |
| A DAX expression for a calculated column applies to every row in each dataset and displays the result in a new column. | When using a measure, the value is calculated based on the set of data that’s been selected by filters, slicers, or other components of visuals in the report. |
| Has row context: the calculation is done row-by-row. | Reacts to filter context: calculated in response to filter and field changes in the report. |
| Static: Stored in memory (RAM), it is pre-calculated and appended to each row in a table then stored in the model. | Dynamic: Makes use of CPU, by changing the calculation each time a filter or slicer changes. It does not create new data in the tables themselves. |
| Calculated when the report is refreshing, or when it is created. | Calculated when added into the report through something like a matrix visual. |
| Increases file size. | It does not increase file size. |
| Visible in the Data View, based on data that is already present in the data model. | Visible when you use a measure in a visual, like a chart or a matrix (like a calculated field in an Excel pivot). |
| More calculated columns lead to more memory consumption, longer refresh time. | Too many measures with great complexity might take a while to respond due to calculations running when changing slicers and filters. |
| It does not give a weighted average of a column, but rather an arithmetic average. | Will give a weighted average. |
| Full name = First Name & “ “ & Last Name | Sales YTD= TotalYTD(Sum(Sales), DateField.[Date]) |
Finally:
When it comes to using Calculated Columns and Measures, it might be helpful to keep the following Rule of thumb in mind:
Rule of thumb  |
|---|
| When you need to filter by the result of a measure, then you need to create a calculated column. |
| When you need to create numerical, calculated fields or aggregation formulas to be used in the “Values” area of visualization, then we recommend using a measure. Use calculated columns when you have fixed values that you want to peg to each row in a table. |
For more great posts, visit our blog page, or enrol in our training.
Leave A Comment