In this blog, we are going to learn about all the differences between a calculated column and a measure. But first, let us learn about them individually.
What is Measure
Measures in Power BI are the summarization of any data. Measures can also be defined as Power BI formulas.
Measures are basically another way of defining calculations in DAX. Measures are used for aggregating values from various rows in a table instead of computing the values for each row.
What is a Calculated Column
A calculated column is just like any other column present in the table. A calculated column basically enables you to add new data to your table in your Pivot Power Data Model. You can simply create a DAX formula to define your column values instead of pasting or importing values into the column.
The formulas present in a calculated column are similar to the formulas in Excel. However, the only limitation is that you cannot create different formulas for different rows in the table. Instead, the DAX formula is applied to all the rows present in the table.
The calculated column is computed at the row level of the table to which it belongs.
Measure vs. Calculated Column
| S. No. | Basis | Measure | Calculated Column |
| 1 | Definition | When you do not want to compute values for each row but, rather, want to aggregate values from many rows in a table. These calculations are measures. | A calculated column is an extension of a table that’s evaluated for each row. It can be created inside and outside Power Query both. |
| 2 | Example | Deriving the total profit for all the products. | Deriving the per-unit profit of a product by subtracting the product’s cost from the sales price of the product. |
| 3 | How they are calculated | Their values are also calculated using DAX formulas and values from other columns. Every calculated measure must contain an aggregation function such as AVG or SUM. Without an aggregation function, the measure’s formula will display an error. | Their values are calculated using DAX formulas and values from other columns. |
| 4 | Ideal For | Ideal for analytical, “summary/ aggregation” information. | Detailed information. |
| 5 | When to Use | Prefer using measures when the size of the model is comparatively larger. | Prefer using a calculated column when the size of the model is comparatively smaller. |
| 6 | Operation | A measure is usually the result of aggregation. | A column is usually the result of the row-by-row calculation. |
| 7 | Storage | Stored temporarily. | Stored in the table. |
| 8 | Consumption | Measure typically consumes the CPU. | A calculated column usually consumes memory. |
| 9 | Context | Limited by Filter Context. | Limited by Row Context. |
| 10 | Computation | At a run time of visual. | On every data refresh. |
| 11 | Is it recalculated when a filter is changed | Yes | No |
| 12 | How the calculations are performed | Vertically | Horizontally |
| 13 | Dependency | A measure depends on the filter applied in the report or in the chart. | A calculated column does not depend on the user’s interaction in a report. |
| 14 | Memory Requirement | Measures recalculate each time the user changes a filter which can cause the report to respond slowly. | A calculated column will consume fewer resources while users interact with your report. |
| 15 | Intermediate calculation (any math function within a formula) | Measures are preferred during intermediate calculations. | Calculated columns are not appropriate for intermediate calculations. |
| 16 | Usage in column addition | We cannot use a measure to write a calculated column. | We can use a calculated column to write a measure. |
| 17 | Preference | You should prefer using calculated columns when using data from other tables in the data model. | You should prefer using measures for aggregating data in computation. |
| 18 | Usage in filtering | Measures cannot be used as filters in a slicer nor as filters at page and report levels. | Calculated columns can be used as filters in a slicer and as filters at visual, page and report levels. |
| 19 | When are the values visible | While using a measure, the values can be seen when they are added to the report. | While using a calculated column, the values can be seen when they are added to the column in the data set. |
| 20 | Usage in other calculations | We can take measures as values in visuals but cannot perform other manipulations like max, min, count, distinct count, etc. | We can take calculated columns as values in visuals and perform other manipulations like max, min, count, distinct count, etc. |
| 21 | Use Case | You can not categorize text or numbers with measures. For example, 0–18, 18–25, and so on. You have to create a calculated column for the same. | If you need to compute the ratio on the aggregates, you cannot use an aggregation of calculated columns. For example, Profit Margin. You will aggregate Profit & Sales and then you will find out the ratio. You cannot find out the PM for each row and then aggregate the same. |
Conclusion
These were all the differences between a calculated column and a measure. We hope you found this blog helpful.
