Calculate() is Like SUMIFS()

March 27, 2023 - by Bill Jelen

As you get started with DAX, you are going to find yourself using the CALCULATE function. This function will perform a calculation while applying any number of filters. =CALCULATE(Sum(Field),Filter1, Filter2, Filter3).

Perhaps you want to calculate sales on Saturdays in January. You might think that you would have to do: =CALCULATE(SUM(Sales[Revenue]),Sales[Weekday]=”Saturday”,Sales[Month]=”Jan”,Sales[Year]=2016).

However, in the pivot table below, cell F4 already has filters applied to it. Cell F4 is limited to January by the month label in D4. Cell F4 is limited to 2016 by the slicer.

There are months down the left side of the pivot table and a slicer for year. With 2016 selected in the slicer, and the active cell on a row for January, you already have two filter applied to this cell: January and 2016.

Figure 1065. Cell F4 is already filtered to Month=Jan, Year-2016.

This simplifies your formula. You don’t have to specify a filter for Month or for Year, because those are already being handled by the pivot table. The formula for the measure in column F is =CALCULATE(SUM(Sales[Revenue]),Sales[Weekday]=”Saturday”).

Rule #1: Calculate() respects the filters already applied to each cell in a pivot table. Those filters can come from slicers, report filters, row labels, or column labels.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mediamodifier on Unsplash