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:

difference between calculated column and measure in Power BI

Then you can write a DAX expression like below:

Total Cost = Sales[Price]*Sales[Quantity]
difference between calculated column and measure power bi

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:

power bi difference between measure and calculated column

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

dax difference between measure and calculated column

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.

See also  Power BI IF NULL then 0

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:

dax difference between measure and calculated column

Write the formula below:

Total Revenue = SUM(Sales[Total Cost])
difference between calculated column and measure in power bi

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

What is the difference between calculated column and measure in Power BI

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.

FeatureCalculated ColumnMeasure
Calculation TimeData loading phaseQuery time
StorageStored in the data modelCalculated on the fly
UsageIn tables, charts, slicers, etc.Mainly in charts and visuals
Formula EvaluationRow-by-rowAggregated over a table
Performance ImpactIncreases model sizeMinimal

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
See also  How to add a column with a dropdown list in Power Query

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:

>