Calculate in power bi secrets – easy to understand!

Calculate in Power BI is a powerful and magical function that performs essential functions. It is straightforward to learn but takes time to master. It changes the filter context partially and does the context transition. With the help of calculate in Power BI, we can add, ignore, or override the filter context.

Let us learn it with the help of an example. I have created a simple data model that used simple Power BI sample data. This model contains two tables, as you can see below.

The first one is the student table and the second one is marks. There is one to many relationship between students and marks tables. I have written one DAX measure to calculate the total obtained marks by using the following formula.

Total obtained marks = SUMX(Marks,Marks[Mid term Marks] + Marks[Final Term Marks])

Drag and drop subjects and Total obtained marks measure on matrix, you will see every subject with its total obtained marks.

Every row in the matrix has its filter context that is subject-wise. In this way, we can see individual total obtained marks for each subject due to the initial filter context.

How to change the initial filter context using calculate in Power BI?

Now calculate will help us to change the initial filter context. It takes a minimum of two parameters, as you can see below.

CALCULATE(,,…)  

The first parameter is the expression, and the second parameter is a filter. You can add more than one filters. Every filter in calculate is a one-column table.

Let’s write one formula using calculate to get total obtained marks for the “Math” subject. 

 Math marks = CALCULATE(
                    [Total Obtained marks],
                    marks[Subjects] = "Math") 

It will produce the following result.

The value of the Math was 304, and now we are getting the same value for each subject if you look at the “Math marks” column. Previously “Subject” filter had 5 different subjects and now calculate has replaced it with “Math.” Every individual subject is replaced with “Math.” That’s why 304 is repeating for every subject.
If we remove the “Subject” filter and add the “Student Name” filter in the matrix, then we will get the following result.

After adding the “Student Name” filter, you can see that the column “Math marks” with different values and no value is being repeated. Why did it happen? The reason is we have applied the explicit filter only on the subject, but there was no explicit filter applied on “Student Name.” In this way, we are getting “Math subject” marks for every student. We are now using two filter contexts in this example.
The first one is the initial filter context that is coming from the “Student Name.” The second one is the override filter context that we applied on “Subject” using Calculate in Power BI.

For more detail about Power BI Calculate, you can see the following link.

https://docs.microsoft.com/en-us/dax/calculate-function-dax

How to ignore filter context using calculate in Power BI?

Till now, we have seen how we can use Power BI calculate to modify the filter context. In this section, you will learn about how to ignore the filter context to write complex formulas.
For the sake of elaboration, I have added “Subjects and Total obtained marks” on the matrix in the Power BI report, as you can see below.

We have one slicer for selecting individual students to see their total obtained marks. Now we have one business situation where we want to know the performance percentage for each subject. To achieve this goal, we have to divide “Total obtained marks” for every “subject” with total marks of all the “subjects.”
In this case, we need to repeat 356 for all the subjects. For that, we have to use “calculate function” to ignore the filter context that is coming from the “Subjects.”

Total marks = CALCULATE([Total Obtained marks],ALL(marks[Subjects]))

With the help of “ALL function” we can ignore the incoming filter context.

There are two types of filter context.

• Internal filter context
• External filter context

 The first one is the internal filter context that coming from the different columns of the matrix. In our scenario, the internal context is the “Subjects” filter. 

The second filter context is coming from the slicer. In our case, it is the “Student Name” slicer.

ALL() function only ignores the internal filter context, but the external filter context will be still active. If you select different students in the slicer, you will see different “total marks” in the matrix. “Total marks” will be different for each student, but it will remain the same for all subjects.  

Performance percentage using Calculate

Let’s write a DAX expression using Calculate with All() function to achieve performance percentage.

 Performance % = DIVIDE([Total Obtained marks],[Total marks]) 

We are now dividing “Total obtained marks” with “Total marks” by ignoring the internal filter context. Maybe this is not a concrete example to show the power of “calculate,” but my aim to make it simple. If the underlying concept is understood, then the target is fulfilled.

Power BI calculate and filter function.

As we have seen the below formula

 Math marks = CALCULATE(
                    [Total Obtained marks],
                    marks[Subjects] = "Math") 

This formula is the syntax sugar of the following expression using the filter function.

Calculate using Filter = 
CALCULATE([Total Obtained marks],
          FILTER(ALL(marks[Subjects]),marks[Subjects] = "Math")
)

Performance-wise both formulas are the same. Filter and All() both are table functions. When we use All() as the first parameter in the Filter function, it produces a unique set of values from the “subject” column. In this example, the output of the filter function is a one-column table that contains a single value “Math.”

Use of Power bi calculate with Allselected()

I have created one situation to calculate student performance percentage using calculate, but it will give me the wrong answer.

Now we need to write the same dax formula to ignore filter context from “Student Name” so that total marks 1681 should repeat with each row.

 Grand total = CALCULATE([Total Obtained marks],ALL(Students[Student Name])) 

We will use the following dax formula to achieve student performance percentage by dividing “total obtained marks” by “Grand total.”

 Student performance % = DIVIDE([Total Obtained marks],[Grand total]) 

If you look at the below output, it looks good because the total is 100%.

If we select a few students using the slicer, it will give us the wrong percentages.

We are getting wrong values because the “Grand total” measure ignores any filter that is coming from the “Student Name” column. Whenever we select any value in the slicer, it will not have any effect on the “Grand total” value. We are still getting 1681.
In the meanwhile “Total obtained marks” are based on selected values from the slicer.

We need to find a way to get an external filter context for “Grand total” as well so that we should get different values instead of all the time 1681.
To fix this issue, we have an important DAX function called Allselected.

 Allselected total = CALCULATE([Total Obtained marks],ALLSELECTED(Students[Student Name])) 

 Allselected performance % = DIVIDE([Total Obtained marks],[Allselected total]) 

Allselected function works the same as you have learned how All() function works to ignore the filter context, but additionally, Allselected() function keeps the external filter in action. This way, we get total marks for those students that we have selected in the slicer.

You can also learn about sumif in power bi

For more useful blogs, please visit Learn DAX