If DAX had a single function worth understanding deeply, it’s CALCULATE. Almost every useful business measure (share of total, year-to-date, sales for one region) is a CALCULATE underneath.
One sentence
CALCULATE works out an expression after changing the filters it runs under.
Canada Sales = CALCULATE([Total Sales], financials[Country] = "Canada")Read it as: take Total Sales, but first set the Country filter to Canada.
Why every row shows Canada
Put Canada Sales in a table next to Country and every row shows the same number, Canada’s total. That surprises almost everyone the first time.
Each row of the table filters Country to its own value: France, Germany, Mexico. CALCULATE then replaces the filter on Country with Country = "Canada". The France filter is gone, so the France row shows Canada.
Filters on other columns stay. If the table also filters Segment to Government, you get Canada’s Government sales.
Three patterns you’ll use every week
1. Sales for a fixed group
Europe Sales = CALCULATE([Total Sales], financials[Country] IN {"France", "Germany"})2. Share of total
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(financials)))REMOVEFILTERS removes every filter on the table, so the denominator is always the grand total.
3. Time comparisons
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))Time intelligence functions return a set of dates, and CALCULATE uses it as the filter.
When you want to keep the existing filter
Wrap the condition in KEEPFILTERS and it combines with the existing filter instead of replacing it:
Canada Only = CALCULATE([Total Sales], KEEPFILTERS(financials[Country] = "Canada"))Now the France row is blank (nothing is both France and Canada) and only the Canada row shows a value.
Try it
The CALCULATE lesson walks through all of this in a practice copy of Power BI Desktop, with instant checks. Microsoft’s reference page for CALCULATE has the full syntax.
Sources
- CALCULATE function, Microsoft Learn
- Filter modifier functions: ALL, REMOVEFILTERS, KEEPFILTERS, Microsoft Learn