In Power BI, you often has to decide whether to create a column or measure to achieve your analytic goals, though they can be used inter-changeablely in a many cases, they are very different in how they function and perform.

Calculated Column

A new column in your data model is essentially a static calculation that can be appended to your table. This new column is derived from existing column(s) and is computed using a formula that you define. Like every other column, once a new column is created, its values are saved in the data model. These newly computed values can then be utilized in visuals, charts, and tables for additional scrutiny and interpretation.

Suppose you need to figure out the total sales. You can create a new column to multiply the price per item with the quantity sold. This new column could then be used to craft a bar graph that displays total earnings segmented by products, categories, or regions.

However, it’s crucial to remember that new column values are unalterable and won’t respond to user interactions with the interface. These calculations are pre-set and will remain consistent unless you deliberately revise them.

And the other key point to note is Calculated columns are row-based calculations, so it will be new static value for each row once you have calculated column created.

Calculated columns in import models have a cost: They increase the model storage size and they can prolong the data refresh time. The reason is because calculated columns recalculate when they have formula dependencies to refreshed tables.

You can write a formula for a calculated column to measure the difference between ListPrice and Dealer Price.

PriceDiff = [ListPrice] - [DealerPrice]

There are three ways that you can use to add columns to a model table:

• Add columns to the source before getting into Power Query. This requires skill and permission that may not be available to end users,
• Add custom columns using M
• Add calculated columns using DAX

Though one can choose the best approach that fits the skills, it is recommended to add custom column in Power Query when possible, that makes the model load in a more optimal way.

Measure

A measure on the other hand, is a dynamic calculation that aggregates data from one or multiple columns in a table. It usually works on aggregated level basis, can be as simple as a sum of sales or can be complex as calculating monthly average in the rolling 12months etc.

With the dynamic feature, measures are not pre-calculated, and they respond to filtering and slicing of data in real-time. they will be evaluated on the fly when being added to the report.

Measures don’t store values in the model. Instead, they’re used at query time to return summarizations of model data. Additionally, measures can’t reference a table or column directly; they must pass the table or column into a function to produce a summarization.

In the previous scenario, if we wanted to create a measure to get the difference between List Price and Dealer Price, let’s call it DiffPrice, it will return error:

Adding some aggregation, the error will go away.

Last but not least, A measure needs to be defined in a table. This is one of the requirements of the DAX language. However, the measure does not really belong to the table. In fact, you can move a measure from one table to another one without losing its functionality.

To Move a measure, you select the measure and measure tools tab will show up in the top menu, then choose from home table and move the measure to a different table that you see fit.

Now, we understand the differences when creating measure and columns, whatif you were give a model with a mix of calculated columns and measure, how do you determine which is measure and which is a column? Looking at its icon.

Top and bottom are calculated column as they always have some calculation sign next to it, that is why we refer it as calculated column. While the middle one looks like a calculator and that is the icon used for measure.

In summary, below are some of the main differences between calculated columns and measure:

• Purpose – Calculated columns extend a table with a new column, while measures define how to summarize model data.
• Storage – Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
• Consumption: Calculated columns mainly* consume memory while measure mainly* consumes CPU.
• Evaluation – Calculated columns are evaluated by using row context at data refresh time, while measures are evaluated by using filter context at query time. For that reason, calculated column can refer directly to other columns, measures can’t.
• Reusability: Calculated columns belong to a table, measure can be moved and used across tables
• Visual use – Calculated columns can be used as slicer while measure can’t