In this tutorial, we’ll be looking at how to use the DAX function CALCULATE in Power BI. You can watch the full video of this tutorial at the bottom of this blog.
CALCULATE is one of the most dynamic functions that can help you add deeper insights to your reports. But to truly understand it, it’s necessary to have a good grasp of row and filter context as well.
Sample Data Used
For the purpose of this tutorial, let’s use a simple dataset that shows Products, Quantity, and Unit Price.
We also have a dimension table that only contains Products.
If we look at the model, we can see that the Products, which appear in our dimension table, are linked to the fact table through a many-to-one relationship.
Row Context Through CALCULATE In Power BI
Row context is one of the primary concepts you have to understand to truly understand what CALCULATE is all about. To help make this clearer, let’s first look at how our dataset looks like in Excel.
Basically, the row context defines what’s happening to every single row. If we were to get the Sales for product A, for example, we would simply put an equal sign, then multiply the values under Quantity and Unit Price.
If we apply that same logic row after row, we get the amount of Sales for each row, allowing us to come up with the Total Sales.
Now, let’s switch to Power BI.
Remember that the same calculation was iterated row by row. There are a number of iterating functions in Power BI, most of them ending with an X. For example, the iterating function for SUM would be SUMX.
As you can see, SUMX requires a table to be referenced, so we’ll use our FactTable where our data is stored.
Then, we’ll multiply the required columns from the FactTable, which are Quantity and Unit Price.
When I hit enter, it will give us our Sales.
Filter Context Through CALCULATE In Power BI
The second thing we have to look at is our filter context, which is another critical component of CALCULATE.
Using CALCULATE means modifying the filter context applied to an expression. So first, we’ll look at how to modify the filter context. This simply means changing the filters on the report.
When applying it to an Excel report, it’s as easy as clicking on the dropdown arrow beside the column name and choosing the filters that you want to apply.
If, for example, I only need the data for Product A, I simply need to tick off A on this filter.
Once I click OK, the report will only show the Sales for Product A.
That’s the second part of the process, which is applying the filter to an expression. In this case, the expression is Sales.
Going back to Power BI, we will need to use CALCULATE to do the same thing. Let’s start by creating a new measure for Sales Product A.
Then, we’ll use CALCULATE and apply the expression, which is Sales.
Next, we’ll modify the filter context by referencing our FactTable.
Then, we’ll have to specify that from the Products column, we only need the sales related to product A.
Now, we have the sales for product A, which is 6,520 – which is the same results we got from our Excel table.
Applying Filter Context Using SUMX
Although our main focus here is CALCULATE, it’s also important to know that this can also be done using other functions. For example, the filtering we did is just like using the Excel function SUMif. But when doing it on Power BI, we can use SUMX.
To do that, let’s create another measure called Sales Product A v2.
We want to go row by row to find any details about Product A, so we’ll apply SUMX. Normally, our first instinct would be to reference our FactTable because that’s where our data will be coming from.
But if we do this, we won’t be able to apply any filters anymore. So instead of referencing the table first, we need to prioritize using the FILTER function. FILTER is another iterator that allows us to look at data row by row.
Once we have the FILTER function in place, that’s the only time we reference the FactTable. Then, we’ll specify what filters we want to use, so we’ll reference items from the Products column relating to product A.
Next, we specify the expression, which is Sales.
If we bring in this measure to our results, you’ll see that it shows the same Total as the previous measure we created.
Now, you’re probably asking why we need CALCULATE if there are other functions we can use. The truth is, CALCULATE has a lot more uses than what we’ve discussed so far.
Other Uses For CALCULATE In Power BI
To show you other ways to use CALCULATE, let’s add another column to our dataset for percentage sales. On Excel, that means dividing the Sales by the Total Sales.
I will also have to lock the cell for Total Sales so that the same figure is applied to the rest of the cells in the column.
Once I hit Enter, we’ll see percentages that would add up to 100%.
If I change the filters and only get the data for product A, you’ll see that the column for percentage sales still retains the same percentages.
When you switch to Power BI, you can do something like this through the use of CALCULATE.
Let’s create a new measure under our key measures. We’ll start with All Sales first, which is equivalent to Total Sales on our Excel report. We’ll use CALCULATE for this.
Then, we’ll use Sales as our expression.
Looking back at our Excel table, remember that we had to lock the cell containing Total Sales to make sure that despite the filtering, we will still be using the same Total across the entire column. In our Power BI report, we’ll use ALL to do that.
Once I click enter, we bring in all the sales for all the products.
This makes it easier to get our percentage sales. So let’s create another measure for that.
Then, we’ll use DIVIDE, again dividing our Sales with All Sales the same way we did in Excel.
Once we hit enter, we’ll have our percentage sales, which adds up to 100%.
Conclusion
These are just some of the many uses of the CALCULATE function. In fact, it can also be used in time intelligence calculations.
You can go through our other tutorials that show these other uses. It is basically one of the functions that we can’t do without if we want our reports to show as much insight as possible.
All the best,
Mudassir
[youtube https://youtu.be/LLgkuW_jCts?rel=0&w=784&h=441]
***** Related Links *****
Power BI CALCULATE: Key To Advanced DAX Formulas
Using Calculate Function Inside Power BI
Calculate Percentage Margin In Power BI Using DAX
***** Related Course Modules *****
DAX Formula Patterns
Mastering DAX Calculations
Time Intelligence Calculations
***** Related Support Forum Posts *****
CALCULATE Function With Several Filters
Calculate Function Not Working
How To Utilise A Boolean Expression Within The CALCULATE Function
For more on CALCULATE function, please see here…
