DAX CALCULATE function
Evaluates an expression after changing the filters it runs under.
CALCULATE is the most important function in DAX. Almost every business measure (share of total, year-to-date, sales for one region) is a CALCULATE underneath. A filter argument on a column replaces the existing filter on that column.
Syntax
CALCULATE(<expression>[, <filter1>[, <filter2>]…])| Argument | Required | Description |
|---|---|---|
| expression | Required | The expression to evaluate, usually a measure like [Total Sales]. |
| filter | Optional | A condition on a column (financials[Country] = "Canada"), a table expression, or a modifier such as REMOVEFILTERS, ALL or USERELATIONSHIP. Several filters are combined with AND. |
Returns: The value of the expression under the new filters.
Examples
Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).
1. Canada sales, whatever the row
Canada Sales = CALCULATE([Total Sales], financials[Country] = "Canada")| Country | Canada Sales |
|---|---|
| Canada | 24,887,654.88 |
| France | 24,887,654.88 |
| Germany | 24,887,654.88 |
| Mexico | 24,887,654.88 |
| United States of America | 24,887,654.88 |
| Total | 24,887,654.88 |
Every row shows Canada's sales: the filter on Country replaces the row's own country. That surprises everyone the first time.
2. Share of total
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(financials[Country])))| Country | % of Total |
|---|---|
| Canada | 20.96% |
| France | 20.51% |
| Germany | 19.80% |
| Mexico | 17.65% |
| United States of America | 21.08% |
| Total | 100.00% |
The denominator removes the country filter, so it is always the total of all countries.
Good to know
- To keep the existing filter and add to it, wrap the condition in KEEPFILTERS.
- Filters on other columns stay: in a table by Segment, Canada Sales shows Canada's sales for that segment.
- A measure reference inside an iterator has a hidden CALCULATE: the current row becomes a filter (context transition).
Related functions
Official reference: CALCULATE function on Microsoft Learn