Sumif power bi equivalent in dax with simple examples

Sumif power bi equivalent in dax can achieve in different ways. Unfortunately, there is no such function available in power bi dax. But it is not difficult to get the required result with the help of workarounds.

In this article, I will try to implement power bi sumif with two compelling functions in dax that are “Calculate” and “Filter.”

The purpose of this post is to share solid concepts regarding sumif in dax with simple real-life examples.

Let’s take the sample data of student and subject marks, as you can see below.

Problem Statement

We need to get a total of midterm marks for every student. We have to sum up only those subjects which have midterm marks greater than 15.

Sumif power bi with Calculate

Calculate in power bi is the core of dax, and it is worth to learn it. You can achieve beneficial and complex results with Calculate. Let’s first write the formula, and then we can explain it step by step.

 sumif = CALCULATE(SUM(Marks[Mid term Marks]),Marks[Mid term Marks] > 15)

Calculate function in dax takes a minimum of two parameters.

The first parameter is an “expression,” and the second parameter is the “filter.”

As you can see in the formula, we have provided the first parameter SUM(Marks[Mid term Marks]), which is straightforward to understand. It is aggregating midterm marks for all individual students. But we need to get total midterm marks for only those subjects which fulfill the condition “Marks[Mid term Marks] > 15”.

You can see in the above figure about the relationship between student table and marks table.

Every student has its filer context, and that’s why we can see filtered midterm marks for every student.

Power bi sumif with Filter function

We can achieve the same result with the help of Filter function in dax. The filter function in dax is the frequently used function to write complex dax expression. If you want to get excellence in writing dax, then it is essential to learn Filter function in power bi.

The filter function is a table function, and it returns a table at the end. The filter function takes two parameters. The first parameter is a table, and the second parameter is a filter expression. We can provide the first parameter as a complete table or one-column table using the “All()” function in dax.

Let’s write a dax expression equivalent to sumif power bi.

 sumif w filter = SUMX(FILTER(Marks,Marks[Mid term Marks] > 15),Marks[Mid term Marks])

Sumx is an iterative function that always needs a table as a first parameter. It iterates through the table via row context and performs the calculation on the second parameter.

In the formula, the first parameter is “FILTER(Marks, Marks[Mid term Marks] > 15)”. By applying the filter function, we are getting midterm marks aggregation for those subjects which have midterms marks greater than 15.

We can create a dynamic table by clicking on the modeling tab and then click on “New Table.” “Mid term Marks” column contains only those marks which are greater than 15.

Now we got a required table for the “Sumx” function. The second parameter of sumx is the expression “Marks[Mid term Marks].” Sumx will iterate through the new dynamic table and sum up midterm marks.

Let’s drag the measure “sumif w filter” on the report and put “Student name” from the student table on the visual. You can see all individual students with their aggregated midterm marks.