Prep 1: Columns and Measures

In Power BI, calculated columns and calculated measures are both powerful features that allow you to perform calculations and add new columns to their data model. However, there are some key differences between the two so we will cover this early in case you get caught out:

Calculated Columns:

• Definition: A calculated column is a column that is added to a table in the data model. It uses a formula or expression to calculate a value for each row in the table. The calculated column’s value is stored in the data model, occupying space in memory.
• Calculation Scope: Calculated columns operate at the row level and can be used in visualisations, filtering, and sorting operations.
• Computation: Calculated columns are computed during data loading or refreshing, and the calculated values are stored in memory. These values are then used to perform calculations in queries and visualisations.
• Usage: Calculated columns are useful when the calculated value needs to be accessed repeatedly, such as for sorting or filtering. They are typically used for static calculations or for creating new dimensions based on existing columns.

Calculated Measures:

• Definition: A calculated measure is a calculation that is performed on aggregated data in a visual or report. It uses a formula or expression to derive a value based on aggregated measures or other calculations. Calculated measures are defined in the data model or within specific visuals.
• Calculation Scope: Calculated measures operate at the aggregate level, combining and summarising data across multiple rows and columns. They are used in visualisations and reports to perform calculations on aggregated data.
• Computation: Calculated measures are computed dynamically during query execution or when interacting with a visual. They don’t occupy memory space and are recalculated on the fly based on the context of the visualisation or report.
• Usage: Calculated measures are ideal for performing dynamic calculations on aggregated data, such as sums, averages, ratios, or percentages. They are commonly used in measures, key performance indicators (KPIs), and in creating more complex calculations based on aggregated values.

In summary, calculated columns are added as new columns to a table in the data model and operate at the row level, while calculated measures are calculations performed on aggregated data in visuals and reports and operate at the aggregate level.