Function CALCULATE

CALCULATE is one of the most commonly used functions in DAX. It is used to calculate expressions, usually given by some aggregation function, with use of one or more filters. It is not incorrect to use CALCULATE without a filter, but that’s not much point. The strength of this function is the ability of combining expression with a range of DAX functions that narrow set of data to perform aggregation with…

First of all, let’s show the syntax of this function:

CALCULATE (<expression>,<filter1>,…,<filtern>)

The first function argument is an expression written by using an aggregation function (SUM, SUMX, AVERAGE, AVERAGEX etc.). All subsequent arguments are filters, and for this purpose we can use a function or a actual statement for defining logical condition which makes a filter. In following examples I will show some possible applications of the function calculate:

PrometSvihPiva:=CALCULATE(SUM([IZNOS]),Artikli[KLASIFIKACIJA]=”Piva”)

PrometJelenPiva:=CALCULATE(SUM([IZNOS]),Artikli[KLASIFIKACIJA]=”Piva”,Artikli[ROBNA MARKA]=”JELEN”)

PrometJelenPivaKolVecaod50:=CALCULATE(SUM([IZNOS]),Artikli[KLASIFIKACIJA]=”Piva”,Artikli[ROBNA MARKA]=”JELEN”,Transakcije[KOL]>50)

First formula is used to calculate turnover of all sold beers within a retail store (sum of all amounts, where amount is a product of quantity and price), second one is made to calculate turnover of „JELEN“ beer (famous Serbian brand), and third has an aim to calculate previous expression in a case where invoice quantity was bigger than 50.

In following recipes we will be talking about more DAX functions that could be used as filters in CALCULATE…