Sign in or start a free trial to avail of this feature.
5. Using the Calculate Function
Calculate is almost certainly the most powerful function in DAX. It filters the dataset and evaluates another formula, in one function. We’ll introduce Calculate in this lesson.
Understanding Calculate (00:05)
CALCULATE is possibly the most important function in DAX. CALCULATE applies one or more filters to a data set, then evaluates any DAX formula with that filter applied. The principle is similar to Excel functions such as SUMIF or AVERAGEIF.
Lesson Goal (00:30)
In our data set, the highest selling product is called Melacaine. Our goal in this lesson is to compare sales of Melacaine with overall sales by creating several measures using CALCULATE.
Calculating Sales for a Single Product (00:37)
The CALCULATE formula takes two arguments. First is an expression to evaluate, second is one or more filters to apply before evaluating the expression. The expression can be a function or a measure.
For example, we use CALCULATE to identify the total sales for a single product, Melacaine. The expression to evaluate is the sum of sales. The filter is that the product name should equal Melacaine. After we create the measure, we can add it to a visualization. This use of CALCULATE lets us view filtered and unfiltered data in a single visual.
Calculating Unit Price for a Single Product (02:25)
We can use a measure as the expression to be evaluated by CALCULATE. For example, we use CALCULATE to identify the unit price for the product Melacaine. In this case, the expression to evaluate is the measure that calculates unit price. As before, the filter is that the product should be Melacaine.
This example illustrates an important syntax convention in DAX. When a formula contains a reference to a measure, the measure name should be contained in square brackets. A column reference should be enclosed in square brackets and preceded by the table name. Including the table name for columns but not measures makes it easy to identify whether a reference in a formula is a column or a measure.
In this lesson, we'll introduce the CALCULATE function. CALCULATE is perhaps the most useful function in the DAX language. CALCULATE filters the data model, and then evaluates any DAX function with that filter applied. If you're familiar with functions like SUM(), IF(), and AVERAGEIF() in Excel, then you'll be familiar with the basic idea behind CALCULATE. Throughout our analysis, we've seen that the top-selling product in our dataset is Melacaine.
Our goal in this lesson is to compare sales of Melacaine with overall sales figures by applying the CALCULATE function to new measures. We'll create a new report page and then create a matrix with Date in the rows well and Sales in the values well.
We'll expand the date down to the quarterly level and also expand the table area.
We'll then go to the format section of the visualization pane, select Grid, and increase the text size to 12.
We want to add another column to this matrix, showing only sales of Melacaine.
In order to accomplish this task, we'll create a new measure.
We'll call it Melacaine Sales and use the CALCULATE formula.
This formula takes two arguments. The first is the expression we want to evaluate and the second is the filter we want to apply. We have the ability to apply multiple filters but, for now, we'll only use one. In this scenario, the expression we want is Sum Of Sales and the filter is the Product Name equal to Melacaine.
Note that we need to put double quotes around the text value.
This measure will calculate the total sales for Melacaine only. We'll press enter to create the measure and we'll drag it onto our matrix.
The matrix now shows overall sales of Melacaine between 2012 and 2017.
As we can see, creating a measure using CALCULATE let's us combine filtered and unfiltered data in the same visualization. As you might expect, we can use the CALCULATE formula to evaluate measures.
For example, we could have evaluated the total sales measure instead of typing out the Sum formula.
Let's look at another example by calculating the Unit Price measure for Melacaine. We'll drag the Unit Price measure from the field list onto the matrix and then create a new measure called Melacaine Unit Price.
We'll again use the CALCULATE formula but this time we'll evaluate the Unit Price measure we created in a previous lesson.
As before, the filter will be that the Product Name is equal to Melacaine.
Before we press Enter to accept this measure, let's take a look at the syntax of this formula.
When we reference a measure in a formula, we place the measure name in square brackets.
When we reference a column name, we enter the table name followed by the column name in square brackets.
In this instance, we could actually remove the table name from the column reference.
However, now it's not clear if Product Name is a column or a measure. For this reason, you should always include the table name when referring to a column We'll now press Enter to create the measure and add it to the matrix. We can see that Melacaine has a price per unit that is well above the average for the other products.
As you can see, CALCULATE is a very versatile function.
It can be used to evaluate any expression that produces a single number. As such, it can evaluate any measure or any of the aggregate functions we discussed previously.
There's a lot more to the CALCULATE function than we've seen here and we'll use it often through the coming lessons. In the next lesson, we'll move on and look at logical functions.