Power BI is a robust business analytics and data visualization tool that empowers organizations to gain valuable insights from their data. However, when it comes to creating calculations, it can be easy to get confused between calculated columns and measures.
While both are used to derive new information from existing data and both calculated columns as well as measures in Power BI can be created using the DAX (Data Analysis Expressions) language, which is a powerful formula language used for deriving new information from existing data.
Calculated columns and measures have distinct functions and are suitable for different scenarios. In this blog, we’ll see the differences between calculated columns and measures in Power BI with examples and when to use each.
What are Calculated Columns?
Calculated columns are a type of column in Power BI that are created by applying a formula to existing columns in a table. These formulas are typically based on data in other columns and can be used to add new information or transform existing data.
In Power BI, when creating a new column using a calculated column, the name of the new column is included as part of the formula that is written in the formula textbox.
However, Calculated columns in Power BI can change when the data is updated, but they are only calculated when the data is loaded into Power BI or when a data refresh occurs. The calculation is then stored as a static value in the column until the next refresh.
This means that if the underlying data changes, the calculated column will not be updated until the next data refresh. When creating a calculated column using a DAX expression, the calculation operates within the context of the current row in that table.
Within a given row, a reference to a column in Power BI will return the corresponding value for that column. It is not possible to directly access the values of other rows, as each row is treated as an individual unit within the dataset.
However, it’s important to remember that calculated columns can utilize a significant amount of RAM. When creating a calculated column, it’s crucial to be mindful of the complexity of the formula used, as more complex formulas can result in intermediate calculations that are stored in memory, taking up precious space.
| Syntax is: “TableName[ColumnName] = DAX expression” | |
What are Measures?
Measures, on the other hand, are dynamic calculations that are created on the fly as the user interacts with the data in Power BI.
Values are not calculated for each row as it was for the calculated column instead, they are based on aggregations of data, such as summing, averaging, or counting, and can be used to answer questions about the data in real-time. Measures are typically created using the DAX (Data Analysis Expressions) language, which is a powerful formula language used in Power BI.
| Syntax is: “TableName[MeasureName] = DAX expression” | |
Selecting Between Calculated Columns and Measures in Power BI
Calculated columns and measures may seem similar, but they have significant differences. A calculated column uses the current row as a context and is computed during data refresh. However, a measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report.
Here are some situations that determine the choice between calculated columns and measures.
- When you need to display results in specific areas of a report, such as a slicer, rows or columns of a pivot table, or axes of a chart, or use the result as a filter condition in a DAX query, a calculated column is the way to go.
- If you want to categorize text or numbers, such as a range of values for a measure or a range of ages of customers, then you also must define a calculated column.
- If you want to display resulting calculation values that reflect user selections and view them in the values area of a pivot table or the plot area of a chart, defining a measure is necessary.
Therefore, defining a calculated column can increase the size of the model, so it’s important to avoid creating too many calculated columns. One way to do this is to use iterator functions, such as SUMX or AVERAGEX, which can help you avoid creating intermediate columns and reduce the amount of memory used by calculated columns.
Example 1:
Let’s say you want to create a new column that categorizes orders into three categories based on the order amount: “Small” for orders less than $50, “Medium” for orders between $50 and $100, and “Large” for orders greater than $100.
This is a scenario where you would need to use a calculated column. You can create a DAX formula that categorizes the order amounts based on their value and returns the corresponding category for each order. Here’s an example:
This formula checks the value of the Order Amount
column for each row and categorizes the order as “Small”, “Medium”, or “Large” based on the value. You can then use this calculated column to group and filter your data based on the order category.
Example 2:
Suppose you want to calculate the percentage of the total order amount for each order ID. You can create a measure called “Order Amount % of Total” using the following DAX formula:
In this formula, the DIVIDE function is used to divide the order amount for each order ID by the total order amount for all order IDs. The CALCULATE function is used to calculate the total order amount while ignoring any filters on the Order ID column so that the calculation is done at the table level. The ALL function is used to remove any filters on the Order ID column.
You can then add this measure to a visual such as a pivot table or a chart to see the percentage of the total order amount for each order ID.
This calculation cannot be achieved using a calculated column because it requires the total order amount to be calculated at the table level, while a calculated column only operates on the current row.
Conclusion
To summarize, calculated columns and measures are both important tools in Power BI for deriving new information from existing data.
However, they serve different purposes and have different use cases. Calculated columns are static and are calculated once when the data is loaded, while measures are dynamic and are calculated on the fly as the user interacts with the data and calculated columns increase the mode size, unlike measures.
By understanding the differences between calculated columns and measures, you can choose the right tool for the job and create more effective visualizations in Power BI. Also, have a look at our blog on Datasets Modes in Power BI: Direct Query VS Import.
