Top 21 Differences Between Measure & Calculated Column

0
58
measure-vs-calculated-column

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.BasisMeasureCalculated Column
1DefinitionWhen 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.
2ExampleDeriving 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.
3How they are calculatedTheir 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.
4Ideal ForIdeal for analytical, “summary/ aggregation” information.Detailed information.
5When to UsePrefer 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.
6OperationA measure is usually the result of aggregation.A column is usually the result of the row-by-row calculation.
7StorageStored temporarily.Stored in the table.
8ConsumptionMeasure typically consumes the CPU.A calculated column usually consumes memory.
9ContextLimited by Filter Context.Limited by Row Context.
10ComputationAt a run time of visual.On every data refresh.
11Is it recalculated when a filter is changedYesNo
12How the calculations are performedVerticallyHorizontally
13DependencyA 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.
14Memory RequirementMeasures 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.
15Intermediate calculation (any math function within a formula)Measures are preferred during intermediate calculations.Calculated columns are not appropriate for intermediate calculations.
16Usage in column additionWe cannot use a measure to write a calculated column.We can use a calculated column to write a measure.
17PreferenceYou 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.
18Usage in filteringMeasures 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.
19When are the values visibleWhile 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.
20Usage in other calculationsWe 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.
21Use CaseYou 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.

Previous articleDAX Engines – The Formula Engine and The Storage Engine
Next articleIntroduction To Slowly Changing Dimensions (SCD) in Power BI
A learner, dreamer, and passionate about modern tools and technology like Excel 365, Power BI, SQL, HR Analytics, Six Sigma, WordPress and Visuals to name a few. Total Experience 15+ years including more than a decade of experience in S&P Global and ongoing 4+ years of experience with fast-growing startups & few MNCs. Clients served: Startups, MSMEs to a few of the Big 4 Consulting firms. Trained more than 2500 professionals in the last 4 years on various skills (Analytics, Content, WordPress, Finance, Entrepreneurship, etc). Last but the most important - mother of an awesome kid!

LEAVE A REPLY

Please enter your comment!
Please enter your name here