Concept Breakdown
The CALCULATE function is one of the most powerful and fundamental functions in DAX. Its primary role is to modify the filter context in which an expression is evaluated. When CALCULATE is used, it first evaluates its filter arguments and applies them. If a new filter argument conflicts with an existing filter in the current context (e.g., trying to filter by 'United States' when the current row context is 'Canada'), the new filter from CALCULATE replaces the existing one. This behavior, known as 'context transition' or 'filter replacement,' allows you to override the natural filtering of a visual or row and evaluate an expression under a specific, fixed set of conditions. This is incredibly useful for creating comparative analyses, such as comparing individual categories against a total or a specific benchmark, irrespective of the current selection. It effectively creates a 'what if' scenario for your calculations.
What You Will Learn
Understand when to use CALCULATE, AVERAGEX, SUMX in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a beginner-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided file. Open the 'Tallest Buildings - Replace Filters.pbix' file.
In the report, observe the matrix showing countries and the average height of buildings.
Create a measure called `Avg height US` which calculates the average height of buildings where the `Country[Country]` column equals "United States".
Display `Avg height US` in the matrix. The figure for United States should appear next to each country.
Create a measure called `Avg height vs. US` which subtracts `Avg height US` from `Avg Height` (the base average height measure).
Display `Avg height vs. US` in the matrix and apply some conditional formatting to it. It should be easier to see which countries have taller or shorter buildings compared to the United States.
Create a measure called `Avg floors` which uses the `AVERAGEX` function to calculate the average of `Floors above ground` plus `Floors below ground`.
Create another measure called `Avg floors US` which calculates the same value as `Avg floors` but specifically for buildings in the United States.
Create another measure called `Avg floors vs. US` which works out the difference between the `Avg floors` and `Avg floors US` measures.
Display the `Avg floors` and `Avg floors vs. US` measures in the matrix and apply conditional formatting to the latter. Each country should be compared with the value for United States.
Create a measure called `Total floors` which uses the `SUMX` function to calculate the sum of `Floors above ground` plus `Floors below ground`.
Create another measure called `Total floors US` which calculates the same value as `Total floors` but for buildings in the United States.
Create another measure called `Total floors as % of US` which divides `Total floors` by `Total floors US`.
Display the `Total floors` and `Total floors as % of US` measures in the matrix.
Save and close the report.
Starter DAX
Avg Height =
AVERAGEX(
Buildings,
'Buildings'[Height above ground]
)Expected Outcome
A matrix displaying various average and total building statistics for different countries, with specific measures showing comparisons against 'United States' values, and conditional formatting highlighting differences to easily identify countries with taller/shorter buildings or higher/lower floor counts relative to the US.