Concept Breakdown
The CALCULATE function is a foundational DAX function that allows you to change the filter context for a specific expression. It's crucial for creating advanced metrics like ratios, percentages of totals, and 'what-if' analyses. By combining CALCULATE with functions like ALL, you can remove existing filters from a column or table, enabling calculations that reference a grand total or a specific subset of data regardless of current visual filters. This exercise demonstrates how to remove filters for an overall total and how to apply new, specific filters for comparative analysis within CALCULATE.
What You Will Learn
Understand when to use CALCULATE, ALL, DIVIDE, SUM in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-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 Data: Download and unzip the provided Excel file containing Premier League results.
Load Data: Create a new Power BI file and load both tables from the Excel workbook.
Create Base Measure: Create a new measure named `Total Points` to sum the points column.
```dax
Total Points = SUM('Premier League Results'[Points])
```
Calculate Proportion of Total Points: Create a new measure named `Proportion of Total Points`. This measure should divide `Total Points` for the current nationality by the `Total Points` for all nationalities (using `CALCULATE` with `ALL` to ignore the nationality filter).
Calculate Ratio to English Managers' Points: Create a new measure named `Ratio to English Managers`. This measure should divide `Total Points` for the current nationality by the `Total Points` scored by teams with English managers (using `CALCULATE` to apply a filter for 'England' as the manager nationality).
Build Table Visual: Create a new table visual in your report.
Add Fields to Table: Add `Manager Nationality` to the table. Then, add the `Total Points` measure, and the two new ratio measures (`Proportion of Total Points` and `Ratio to English Managers`) to the table.
Format Measures: Format `Proportion of Total Points` as a percentage and `Ratio to English Managers` to two decimal places.
Save File: Save your Power BI file as 'We miss Alex.pbix'.
Starter DAX
Total Points = SUM('Premier League Results'[Points])Expected Outcome
A table visual displaying each Manager Nationality, their respective Total Points, the Proportion of Total Points (as a percentage of the grand total), and the Ratio of Total Points for that nationality compared to the Total Points won by English managers.