The CALCULATE function is used to modify the context at which filters modify an expression. For instance, if you wanted to see the total sales generated in the "central" region specifically, a slicer could help you with this. CALCULATE could also do this sort of filtering, but in a formula, overriding all other filters applied to the visual .
To truly understand how to use CALCULATE , you need to grasp the concept of filter context. Filter contexts are a set of filters applied to your data before a DAX expression is evaluated. Simply put, it's a set of rules which determine the what rows are visible to your calculation. The filter context could be set by several things like the visuals on a report, report and page level filters or functions like Filter(). The CALCULATE function is used to override the set filter context so you can specify what filter you want to apply to your measure.
It might be helpful to think of this like a Fixed LOD in Tableau with how it allows you to calculate values at a specific level of granularity, regardless of the filters applied in the view. CALCULATE is similar and more flexible but requires explicitly managing the filter context using functions like ALL, ALLEXCEPT, or specific column filters.
The syntax for the CALCULATE function is given below:
CALCULATE ( Expression, [Filter 1], [Filter 2] ...... )
Expression: anything you want to evaluate which returns a single value e.g. Sum ([Sales]).
Filter: a Boolean expression or a table expression that modifies the context of the calculation.
A demonstration of using CALCULATE in PBI will help to drive home the point. Below is the table we will be using to perform the analysis.
Assuming you wanted to see the total sales for strawberry lemonade, this is the dax calculation you would write:
This will result in the total sales of strawberry lemonades counted regardless of whatever other filters that are in view, in this case, the flavour.
You can also include multiple filters arguments within the CALCULATE function, with each argument working as an AND statement where they must both be true before being included in the aggregation.
This function will sum up the total Large Mint sales of lemonade and ignore all other filters in view.
REMOVEFILTERS()
Some times, you don't want your CALCULATE function to overwrite an existing filter, this is where REMOVEFILTERS() comes into play. They're particularly useful when you have a filter on a column that you want to preserve while calculating a total for a different reason. For example, the percentage of total sales for each flavour for a specific cup size.
This measure will calculate the percentage of total for each flavour relative to the total sales for that specific cup size.
From the table above, you can see that the filter was only removed for the flavours, while preserving the the cup size filter. The calculation returns a 1.00 for each cup size total because you're essentially dividing total sales by total sales, but gives you what percentage of the flavours in regards to cup size.
ALL()
The ALL() function removes all filters from a column or table
The ALL() function completely removes all filters from the sales table so that the CALCULATE function always returns the total sales of all lemonade products across all flavours and cup sizes.
This makes it a great choice for measures that need to compare to a complete, unfiltered grand total.