Excel: Calculate() is Like SUMIFS()

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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, Filter 2, Filter 3).

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.

  1. 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.

For more resources for Microsoft Excel