CALCULATE
Evaluates an expression in a filter context that you change.
CALCULATE(<expression> [, <filter> [, <filter> [, ...]]])Returns: The value of the expression (a scalar).
What it does
CALCULATE evaluates a measure or expression after changing the filters that apply to it. A finance team uses it to ask questions such as "total sales for online orders only" or "company-wide sales, ignoring the store slicer." Almost every useful measure in a Power BI or Excel model is a CALCULATE somewhere, even when you do not type the word.
Syntax and arguments
- expression: the calculation to evaluate, such as
SUM(Sales[SalesAmount])or a measure name like[Total Sales]. Required. - filter (optional, repeat as needed): a Boolean condition on one column, a table filter such as
FILTER(...), or a modifier such asALL,REMOVEFILTERS,KEEPFILTERSorUSERELATIONSHIP.
All the filters are applied together. A Boolean filter on a column replaces any filter already on that column, unless you wrap it in KEEPFILTERS.
Example
Assume the measures Total Sales and Total Cost from the fundamentals page. The business wants online channel sales, whatever the rest of the page shows for channels.
Online Sales =
CALCULATE (
[Total Sales],
Channel[ChannelName] = "Online"
)
CALCULATE evaluates Total Sales with the channel filter set to Online. In a table with one row per store, each row shows that store's online sales. The Online filter replaces any channel slicer, so the figure stays online even when the user selects Store.
Example: a second use
A controller wants each store's online sales as a share of the company's total sales across all channels.
Online Share of Sales =
DIVIDE (
[Online Sales],
CALCULATE ( [Total Sales], REMOVEFILTERS ( Channel ) )
)
The denominator removes the channel filter and keeps every other filter on the page, such as the date and the store. The result is a percentage between 0 and 1 that formats well as a percent.
Things to watch
- A Boolean filter replaces the existing filter on its column. A channel slicer is overridden. Use
KEEPFILTERSwhen the slicer must still count. - A Boolean filter names one column and cannot call a measure. For conditions that need a measure or two columns, use a table filter with
FILTER. - Filter arguments are evaluated in the original context, then applied together as an AND.
- When CALCULATE runs inside a row context (an iterator or a calculated column), each row becomes a filter. On a large table this is slow.
- A filter on a dimension reaches the fact table through the relationship. A filter on the fact table does not reach the dimension.
In business terms
CALCULATE answers every "this, but only..." question: a channel or region view, sales excluding returns, a fixed scenario set against actuals, or a total scoped to one legal entity. It sits behind most variance, budget and share-of-total measures in month-end and board reporting.