Top 21 Differences Between Measure And Calculated Column

0
218
measure-vs-calculated-column

You are interested in Power BI and hence you are here. In this blog, we are going to learn about all the differences between Measure and Calculated Column in Power BI. Before we discuss the differences, let’s understand Calculated Column and Measure.

What is Measure

Measures in Power BI are the summarization of any data. They 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. 

Besides this blog, we suggest one more interesting read at Microsoft website about how to create measures for data analysis in Power BI Desktop

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, you apply the DAX formula 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. We can create it both inside and outside Power Query.
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.
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.We use DAX formulas and values from other columns to do the value calculations.
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
12

How the calculations are performed

VerticallyHorizontally
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.
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.
15Intermediate calculation (any math function within a formula)We prefer Measures 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.
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.
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.
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, we see the values 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.
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 Measure and Calculated Column. We hope you found this blog helpful. Do you want to know how to optimize DAX queries and Power BI Reports?

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