• Excel Book Excerpt

Excel Calculate() is Like SUMIFS()

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

Calculate() is Like SUMIFS()

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.

LE10001008.jpg  

Figure 974 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy