CALCULATE – “merging” of other functions (DAX – Power Pivot, Power BI)

This is about CALCULATE, one of most important functions in DAX. This function typically puts together some other functions, when some is used for aggregation (SUM, AVERAGE, FIRSTDATE…) and some used for filtering (FILTER, SAMEPERIODLASTYEAR…).

So, in general, it applies some aggregation, but calculate it for selected data only.

For demonstration look at this table, showing info about revenues for some articles:

We are going to summarize prices for all Strawberry ice creams. The function is:

• Strawberry ice cream price = CALCULATE(
     SUM(‘Table'[Price]);
     FILTER(‘Table’;’Table'[Item]=”Strawberry ice cream”))

Because:

• SUM says we are summarizing (not making average etc…)
• FILTER only applies calculation to some rows
• CALCULATE puts it together

And the result is:

Something more:

• You can use multiple filters at the same time in CALCULATE
• Typical use of CALCULATE is with Time intelligence functions