For this tutorial, I’ll cover how the CALCULATE function works and why it’s so crucial in Power BI. I’ll also show you some examples of its application.
You can easily branch out to time intelligence calculations by using the CALCULATE function. Once you apply this technique, you’ll be able to produce high quality insights inside Power BI.
Using The Calculate Function In Power BI
In this example, I’ll show you how to work out the percentage of the total sales by using the CALCULATE function in Power BI.
Before I start, I’ll show you the existing data in the report. The initial context is the Product Name, and then you can see the Total Sales column.
Since I want to calculate the percentage of total products, I need to work out the product sales first. I’ll create a new measure for All Product Sales, and I’m going to start the formula with the CALCULATE function.
Based on the IntelliSense description above, I’ll have to enter an expression and filter after the function. I’ll start the calculation with Total Sales because I need to calculate all the sales.
I also need to remove any filter or context from the Product Name column. To do that, I’ll add the ALL function and reference the Product table. This is totally fine since the description shows that I can add either a table or column.
I’ll press Enter to finish the formula, and then bring it into the table.
Now, check out the results in the All Product Sales column. You’ll see that the result is applied to every single row in the table. The formula literally calculates the total sales. However, I have changed the context in the calculation. In addition to that, I removed the filter from the Products table by using the ALL function.
Using Measure Branching
In this example, I’ll show you how to calculate the % of Products Total using the DIVIDE function. I’ll also apply some measure branching in this example.
First, create a new measure and divide the Total Sales by All Product Sales.
As you can see, I was able to branch out into the % of Products Total using the previous measure that I have created. The initial results are in decimal format so I’ll quickly convert it to percentage.
I don’t need to keep the intermediary calculations that we used. I can remove them all because the formula will remember everything that has been used in the measures.
I can just copy the existing table and paste it beside the original table, and then turn it into a visualization. But before that, I’ll delete the Total Sales and All Product Sales columns. Here’s what it looks like now.
***** Related Links *****
Common Time Intelligence Patterns Used In Power BI
Time Comparison For Non Standard Date Tables In Power BI
Implementing DAX Measure Groups Into Your Reports
Conclusion
This is a very simple example of measure branching using the CALCULATE function.
To sum up, the CALCULATE function changes the context of your calculation in Power BI.
When you run time intelligence calculations in Power BI, you also need to change the context. This is how the CALCULATE function is relevant when analyzing and showcasing effective reports.
Sam
