Syntax
CALCULATE(<expression>,<filter1>,<filter2>…)
Tutorial with example
In this tutorial we go through in a series of videos through the function CALCULATE.
Calculate Part1:
Calculate Part 2:
Download example file:
Download Power BI file (Part 1): here.
Download Power BI file (Part 2): here.
Common CALCULATE examples:
1. SUMIF, COUNTIF
CALCULATE is among other things, the function you would use instead of SUMIF or COUNTIF. In this video I will show you how to use CALCULATE as an equivalent of the excel functions SUMIF and COUNTIF.
Download example file:
No download file available for the sumif video. Please use the northwind dataset to practice.
2. RUNNING TOTALS, CUMULATIVE TOTALS
In this video I will show how to calculate running totals or also called cumulative totals using CALCULATE and FILTER:
Download example file:
Download Power BI file (Part 1): here.
Remarks
If the data has been filtered, the CALCULATE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
Hi Ruth, Good insights for me there in your two CALCULATE videos – thanks a lot! Just checking: For the 2nd video – would it be okay if I understood it as – you start with the filter coming from the row filter 1st. Then for FILTER as the second argument in CALCULATE, that this subsequent filtering only happens 2nd. So for example for “Gorgonzola Telino” it starts off with only this row filter so that’s why when you apply CALCULATE’s filter you get a sum for this one only. FILTER in this instance is only iterating over one row of the source table? For the next cheese “Gudbrandsdalsost” the 1st filter per the row results in the “Gudbrandsdalsost” row and the 2nd filter in CALCULATE would result in blank because that’s not “Gorgonzola Telino” or “Queso Cabrales”. Would it be okay to understand it as working that way?