Difference between a calculated column and measure

Question

What is the difference between a calculated column and measure

Daniel Wright · Answer

Certainly! The difference between a calculated column and a measure is fundamental when working with data in tools like Power BI, Excel, or SQL. Let's break it down: 1. Calculated Column : - A calculated column is a new column that you add to a table in your dataset. - It is created by defining a calculation formula that operates row-by-row to compute a value for each row in the table. - The calculated column becomes a part of the underlying data and is stored in the data model. - Calculated columns are great for scenarios where you need to create a new attribute based on existing data, like concatenating strings, deriving a new value, or applying simple transformations. 2. Measure : - A measure, on the other hand, is a calculation performed on the fly at query time, usually based on aggregated data. - Measures are used in summarizations (like totals, averages, counts) or to create meaningful insights from the data. - Measures are dynamic and don't persist in the underlying dataset; they are calculated on the results of queries in real-time. - They are commonly used in creating key performance indicators (KPIs), aggregating data, or performing complex calculations that involve aggregation functions. In a nutshell, a calculated column is calculated and stored at the row level within the data model, while a measure is a calculation performed on aggregated data based on the context of the query. For example, if you have a Sales table and you want to calculate total revenue for each sale, you might create a calculated column like 'Revenue = Quantity * Price'. However, if you want to calculate the average revenue per month across all sales, you would use a measure like 'Average Revenue = AVERAGE(Sales[Revenue])'. I hope this explanation clarifies the difference between calculated columns and measures for you. Let me know if you need more information or examples!