Using Calculate Function Inside Power BI

Using Calculate Function Inside Power BI

No comments

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.

calculate function power bi

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.

calculate function power bi

I’ll press Enter to finish the formula, and then bring it into the table.

calculate function power bi

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.

calculate function power bi

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.

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

Membership Banne
Center of Excellence

***** 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

***** Related Course Modules *****
Power BI Super Users Workshop

Time Intelligence Calculations
Ultimate Beginners Guide to Power BI

***** Related Support Forum Posts*****
CALCULATE Function – The Most Important Function In DAX
Understanding The CALCULATE Function – DAX In Power BI
Calculate Function

For more CALCULATE function queries to review see here…..

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.