CALCULATE

CALCULATE is a DAX function that evaluates an expression in a context modified by filters.

Syntax

CALCULATE(
   Expression,
   [ Filter, ... ]
)

Argument	Properties	Description
Expression		The expression to be evaluated.
Filter	Optional, Repeatable	A boolean (True/False) expression or a table expression that defines a filter.

Return Values

The value that is the result of the expression.

Remarks

• When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:

  • If the columns (or tables) aren’t in the filter context, then new filters will be added to the filter context to evaluate the expression.
  • If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.

• The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It’s required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

The following Sales table measure definition produces a revenue result, but only for products that have the color blue.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

Blue Revenue =
CALCULATE(
    SUM(Sales[Sales Amount]),
    'Product'[Color] = "Blue"
)

Category	Sales Amount	Blue Revenue
Accessories	$1,272,057.89	$165,406.62
Bikes	$94,620,526.21	$8,374,313.88
Clothing	$2,117,613.45	$259,488.37
Components	$11,799,076.66	$803,642.10
Total	$109,809,274.20	$9,602,850.97

The CALCULATE function evaluates the sum of the Sales table Sales Amount column in a modified filter context. A new filter is added to the Product table Color column—or, the filter overwrites any filter that’s already applied to the column.

The following Sales table measure definition produces a ratio of sales over sales for all sales channels.

Channel	Sales Amount	Revenue % Total Channel
Internet	$29,358,677.22	26.74%
Reseller	$80,450,596.98	73.26%
Total	$109,809,274.20	100.00%

Revenue % Total Channel =
DIVIDE(
    SUM(Sales[Sales Amount]),
    CALCULATE(
        SUM(Sales[Sales Amount]),
        REMOVEFILTERS('Sales Order'[Channel])
    )
)

The DIVIDE function divides an expression that sums of the Sales table Sales Amount column value (in the filter context) by the same expression in a modified filter context. It’s the CALCULATE function that modifies the filter context by using the REMOVEFILTERS function, which is a filter modifier function. It removes filters from the Sales Order table Channel column.

The following Customer table calculated column definition classifies customers into a loyalty class. It’s a very simple scenario: When the revenue produced by the customer is less than $2500, they’re classified as Low; otherwise they’re High.

Customer Segment =
IF(
    CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Customer, Customer[CustomerKey])) < 2500,
    "Low",
    "High"
)

In this example, row context is converted to the filter context. It’s known as context transition. The ALLEXCEPT function removes filters from all Customer table columns except the CustomerKey column.

Related functions

Other functions related to CALCULATE are:

• CALCULATETABLE

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/calculate-function-dax