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.
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.
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.
|Data loading phase
|Stored in the data model
|Calculated on the fly
|In tables, charts, slicers, etc.
|Mainly in charts and visuals
|Aggregated over a table
|Increases model size
Pros and Cons
- Easy to create and manage
- Good for row-level calculations
- Increases the model size
- Not efficient for aggregated calculations
- Efficient for aggregated calculations
- Does not impact model size
- Slightly more complex to create
- Limited to charts and visuals for most use cases
When to Use Calculated Columns vs Measures?
- Row-level Calculations: Use Calculated Columns
- Aggregated Analysis: Use Measures
- Limited Memory: Use Measures to save space
- Ease of Use: Use Calculated Columns for simpler calculations
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
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com