# A New Look At CALCULATE – Power BI

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

***** Related Course Modules *****
DAX Formula Patterns
Mastering DAX Calculations
Time Intelligence Calculations

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...