Difference Between Calculated Column and Measure in Power BI

Calculated columns and measures are two important concepts of Power BI. Understanding the key differences between Calculated Columns and Measures will not only help you make better decisions but also optimize your Power BI model performance. In this Power BI tutorial, we will deep-dive into the difference between the calculated column and the measure in Power BI.

Calculated Column in Power BI

A Calculated Column is a column that you add to an existing table in the data model in Power BI. It’s computed during the data loading phase, and the values are stored in the Power BI model. The formula for a Calculated Column is calculated for each row in a table.

Example

To create a column in the Power BI desktop, click on the New column like below:

Then you can write a DAX expression like below:

Total Cost = Sales[Price]*Sales[Quantity]

The above DAX expression will calculate values by multiplying the two columns Price and Quantity column, from the Sales table.

You can see the output below:

If you add the data in a Table visualization, the Power BI visualization will be like the below:

In the calculated column, the calculation is done row by row in the table.

Another thing we need to remember is that the calculated column stores values in memory like other columns. The calculation is done when refreshing the report on the Power BI desktop.

Measure in Power BI

A Measure is a calculation created using DAX (Data Analysis Expressions) that is performed on the fly as you interact with your reports in Power BI. Measures are used in visuals, pivot tables, and pivot charts. Unlike Calculated Columns, the calculation is not pre-computed.

Example

Measures are not stored in the memory, so these are faster. Click on New measure like below:

Write the formula below:

Total Revenue = SUM(Sales[Total Cost])

If you will add a Card visualization in Power BI, you can see the Power BI measure value like below:

If you need to operate on aggregate values instead of on a row-by-row basis, you must create measures.

Key Differences Between Calculated Column and Measure

Here’s a table that lays out the key differences between Calculated Columns and Measures in Power BI.

Feature	Calculated Column	Measure
Calculation Time	Data loading phase	Query time
Storage	Stored in the data model	Calculated on the fly
Usage	In tables, charts, slicers, etc.	Mainly in charts and visuals
Formula Evaluation	Row-by-row	Aggregated over a table
Performance Impact	Increases model size	Minimal

Pros and Cons

Calculated Column

• Pros:
  • Easy to create and manage
  • Good for row-level calculations
• Cons:
  • Increases the model size
  • Not efficient for aggregated calculations

Measure

• Pros:
  • Efficient for aggregated calculations
  • Does not impact model size
• Cons:
  • Slightly more complex to create
  • Limited to charts and visuals for most use cases

When to Use Calculated Columns vs Measures?

1. Row-level Calculations: Use Calculated Columns
2. Aggregated Analysis: Use Measures
3. Limited Memory: Use Measures to save space
4. Ease of Use: Use Calculated Columns for simpler calculations

Conclusion

Understanding the difference between Calculated Columns and Measures in Power BI is crucial for optimizing your data models and reports. Calculated Columns are best for row-level computations and are calculated during the data loading phase. On the other hand, Measures are perfect for aggregated calculations and are computed on the fly in Power BI.

You may also like:

• Remove blank from Power bi slicer
• Power BI Measure Sum and Subtract Example
• How to use weekday function power bi with example
• Power BI convert hours to minutes