For readers familiar with Power BI, you already know that the CALCULATE function is one of the most important DAX functions in the platform. This function is commonly used, extremely useful, and opens up many doors for your analyses. There are many different ways to use this function, so in this post I will show you how this function works and a few common use cases of it.
Basic Syntax
I like to think about the CALCULATE function in two parts; the aggregation and the filter. The first part of the expression is the aggregation piece. This is where you can place whatever aggregation function you’d like whether it’s SUM(Sales), AVG(Price), or something else. The second portion is your filter criteria. This part will define the data set that you are applying the aggregation in the first part to.
Here is the DAX syntax for the CALCULATE function in Power BI:
CALCULATE(<[expression]>,([filter1],([filter2],([filter...]))))
Because of the ability to apply criteria to an existing table before aggregation you do not have to create individual tables for specific calculations.
Single Filter Criteria
Let’s continue explaining how to use this function with a very basic example. In this example we are wanting to create a measure that will always show us the sum of sales for the East region in our sales dataset. We know that we are going to sum our Sales field but we want to specify to only sum our Sales field when the Region = East. See below:
// East Region Sales =
CALCULATE(
SUM(Orders[Sales],
[Region]="East")
)
Another common use case of using one filter criteria is to find only Sales from a specific year, often the max year. In this example we are being given Sales data at the year level, so we want to find the sum of Sales for the most recent year.
//Sales CY =
VAR max_year = MAX(Orders[Year])
RETURN
CALCULATE(
SUM(Orders[Sales]),
Orders[Year]=max_year
)
Multiple Filter Criteria
Oftentimes you will want to apply more than one criteria to the dataset that you are using for your calculation. In this case, we are going to be calculating the most recent revenues using a specific NCAA conference from an NCAA financials dataset. You can see that I’ve applied multiple criteria for this calculation.
// Big 10 Revenues CY =
VAR max_year = MAX(NCAA_Financials_FCT[Year])
RETURN
CALCULATE(
SUM(NCAA_Financials_FCT[Total Revenues]),
NCAA_Financials_FCT[Year]=max_year,
'Conference DIM'[Conf_Name]="Big Ten Conference"
)
When applying multiple filters for an AND criteria using the FILTER function to add context you will want to use “&&” to add on criteria. See below:
Big 10 Revenues CY =
VAR max_year = MAX(NCAA_Financials_FCT[Year])
RETURN
CALCULATE(
SUM(NCAA_Financials_FCT[Total Revenues]),
FILTER(NCAA_Financials_FCT,NCAA_Financials_FCT[Year]=max_year &&
NCAA_Financials_FCT[CONF_ID]="9")
When applying multiple filters for an OR criteria using the FILTER function you will want to use a “||” to add on criteria. The below function will return all data that is either in the max year or for the [CONF_ID] = 1. See below:
//Big 10 Revenues CY Multi OR =
VAR max_year = MAX(NCAA_Financials_FCT[Year])
RETURN
CALCULATE(
SUM(NCAA_Financials_FCT[Total Revenues]),
NCAA_Financials_FCT[Year]=max_year
|| NCAA_Financials_FCT[CONF_ID]="1
)
CALCULATE Using ALL, ALLEXCEPT
In the examples up to this point we have created fields that will be subject to any filters or fields applied to the measures. Especially when creating specific measures like the ones we’ve made, you might not want these values to change. We can achieve this by using the function ALL and ALLEXCEPT.
The ALL function will compute the expression part of your CALCULATE function on all of the data in the data set regardless of the filters applied to it. See below.
//Revenues ALL =
CALCULATE(
SUM(NCAA_Financials_FCT[Total Revenues]),
ALL(NCAA_Financials_FCT)
)
The syntax above will result in the sum of Total Revenues being shown regardless of any filters. You can see the table below that even when a Conference field is being shown, the Total Revenues for all the data is being shown.
You can include some filter criteria that will always stay applied no matter what other filters are applied to the measure. You can see in the example below that I have specified a certain year to calculate Revenues. Regardless of other years or conference chosen, this value will remain constant.
//Revenues ALL 2017=
CALCULATE(
SUM(NCAA_Financials_FCT[Total Revenues]),
FILTER(ALL(NCAA_Financials_FCT),
NCAA_Financials_FCT[Year]=2017)
)
The ALLEXCEPT function will return all of your data just like the ALL function EXCEPT it will take into account some filter fields as specified. In the example below, I want to see the Total Revenues by conference, but I want the Years to be able to be filtered. You can see in my syntax that I state that Year will be the only field that can filter the dataset that is being aggregated.
//Revenues ALLEXCEPT Year =
CALCULATE (
SUM ( NCAA_Financials_FCT[Total Revenues] ),
ALLEXCEPT ( NCAA_Financials_FCT,
NCAA_Financials_FCT[Year] )
)
Now when I add this field to my table you can see that the year is now being taken into consideration for the ALLEXCEPT Year measure.
While there are other ways to adjust the context in which the CALCULATE function aggregates a dataset, hopefully this has provided you some insight on how to start using this function appropriately.
In Conclusion
The CALCULATE function is extremely helpful in creating more complex calculations that allow for more in-depth insights. This DAX function is one of the more foundational functions for analysts, so hopefully this blog post has given you an idea of how to leverage it for your everyday use.
Keep in mind, there are tons of other ways to utilize this function in addition to the ways described in this blog. These few examples are just a starting point for what you can do with CALCULATE. Now go get CALCULATING!
