How to do Power BI SUMIF in DAX: 2 Easy Functions

Modern businesses operate on a data-driven model and invest heavily in Data Analytics to enhance their understanding of Market Trends. Moreover, stakeholders require data in different formats and at varying levels of detail. Manually performing such tasks will require a good amount of time and resources. Therefore, companies are drawn to business intelligence (BI) tools like Power BI to automate data management. The collection of such useful functions in Power BI is in a library known as DAX.

The DAX (Data Analysis Expressions) library simplifies Power BI users’ Data Analytics and Visualization tasks. Moreover, with DAX, users can execute dynamic Data Management and perform complex logical and mathematical operations with a few clicks. This way, DAX Functions can optimize the overall user experience of Power BI.

This article introduces the Power BI platform and DAX Library and their key features. It also discusses the importance of DAX Functions and the Power BI SUMIF function. The article also provides 2 SUMIF PowerBI Equivalent Functions in DAX and explains their implementations using an example.

What is Power BI?

Microsoft launched Power BI as a Business Intelligence tool that seamlessly visualizes and analyzes data. You can input raw data into this popular platform and generate actionable insights as output. Power BI also allows your various teams to collaborate on large-scale Data Visualization and Business Analytics projects. This way, Power BI supports your data-driven model and enhances your decision-making capabilities. 

Key Features of Power BI

The following features of Power BI are responsible for its vast popularity in today’s market:

• Data Selection: You can use Power BI to select subsets from your huge datasets. This way you can focus on relevant parts of the data and derive better results after analysis. 
• Responsiveness: You can depend on the high responsiveness of Power BI and analyze your data without any delay. It contains Navigation Trees and Dashboards for both iOS and Android platforms. Therefore you can easily zoom into a Report or Graph and have access to selective information in a glitch-free manner.
• Seamless Functionalities: You can work with Power BI’s drag-and-drop functionalities while developing customized reports. Furthermore, this platform understands Natural Language Queries which don’t require you to learn any complex query language.
• Data Connectivity: Power BI uses various connectors to integrate with numerous external Data Sources and Cloud-based tools. This way you can directly load data from the preferred Data Source easily to the Power BI platform for Data Analytics and Visualization. 

Learn more about Power BI.

What are DAX Functions?

Data Analysis Expressions (DAX) is a software library that holds functions and operators which are important to streamline the use of Power BI. You can rely on the rich functions of DAX to create expressions that will perform complex Power BI tasks. For example, you can leverage DAX’s functions to create an expression (formula) that will read an input statement and return one of the specified values. In other words, you can use DAX to generate new information using the data available data.

DAX also has a Function Reference that acts as its metadata. It supplies comprehensive information related to the syntax, parameters, data types, and returns values for the 250+ functions stored in the DAX library.

Key Features of DAX Functions

DAX provides you with the following unique features:

• Measures: Measures are the dynamic expressions for calculation stored in the DAX Function library. The result of Measures transforms according to the input data’s context. You can simply create Measures from the DAX formula bar present on your model designer. Moreover, you can move measures between tables without causing any loss of functionality.
• Calculated Columns: Calculated Columns store the computational results obtained by performing operations on 2 or more columns, during a data refresh. The Calculated Columns are created to perform row-level calculations and build an output column in the table. However, since this feature consumes RAM (Random Access Memory) at a rapid speed, you should always avoid implementing it during the production stage.
• Row-level Security: DAX Functions always maintain the row-level security of your Power BI tables. Therefore, all DAX evaluations provide only a boolean result (True or False). This way only one allowed row set is created at once and no other users are allowed to access it. Furthermore, DAX also depends upon filters to keep the currently active relationship safe.
• High Performance: DAX Functions offer fast data processing and intelligent Dashboards. Moreover, it upgrades your calculations in Power BI and ensures efficient querying of data. You can also connect Power BI with external sources and implement DAX Functions on source data directly.

Learn more about DAX.

Importance of DAX Functions

You can leverage Power BI to generate new Desktop Files easily and utilize them to store your data before analysis. Moreover, you can use this data and generate comprehensive reports showcasing the Data Analysis insights to various stakeholders. However, if you need to take Power BI’s utility a step further and extract advanced-level data patterns, you will require the DAX Library.

DAX contains formulas that enable you to enhance your Data Analytics. For instance, DAX Functions allows you to analyze the profit percentage across product categories along with different timelines. It also provides you with a comparative analysis of year-over-year growth and changing market trends. Therefore, you must understand the working of DAX Formulas. It will support you in getting the most out of your vast datasets. This way, you can implement the Power BI tool and reap its full benefits concerning your data-driven business model. 

What is the SUMIF Function in Power BI?

Microsoft (in Excel) provides you with the SUMIF function which is ideal for calculating the sum of values falling in a range that meets your criteria. For instance, imagine you have a column containing numerical values and you wish to get the sum of column values that are greater than 5. In this case, you can directly get the required sum using the SUMIF formula as follows: 

Result=SUMIF(B2:B25,">5")

The SUMIF function operates on the following syntax:

SUMIF(range, criteria, [sum_range])

The above formula contains the following arguments:

• Range: The range of cells in your table that you wish to evaluate by the criteria.
• Criteria: The criteria can be in the form of an expression, numbers, a cell reference, or text that clearly defines which cells shall be considered while calculating the sum.

However, the Power BI DAX Library does not support the SUMIF Function. Instead, you can use the following 2 Power BI DAX Functions that are equivalent to SUMIF:

• CALCULATE
• FILTER

The implementation of these POWER BI SUMIF equivalent functions is covered in the next section.

Implementing Power BI SUMIF Equivalent Functions

This section will explain the use of Power BI SUMIF equivalent functions using a student marks example. You have to calculate the total sum of midterm marks for each student. However, keep in mind that you must sum up subjects with higher than 15 midterm marks.

You can obtain the above results using the following PowerBI SUMIF functions:

• Power BI SUMIF Equivalent Function:  CALCULATE
• Power BI SUMIF Equivalent Function:  FILTER

Power BI SUMIF Equivalent Function: CALCULATE

The CALCULATE Function in Power BI DAX is useful for achieving accurate results from complex calculations. In the given example, you can use CALCULATE in the following way as a Power BI SUMIF equivalent function:

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

The above syntax of SUMIF in Power BI uses a minimum of two parameters:

• Expression: In the above formula, the first parameter is an expression describing the addition process as “SUM(Marks[Mid term Marks])”. This expression aggregates the midterm marks for all students.
• Filter: The filter is useful for applying the conditional aspect of your SUMIF Function. Here, you need to calculate the total midterm marks for subjects that come under the condition “Marks[Mid term Marks] > 15”.

The output of the Power BI SUMIF equivalent CALCULATE Function is shown in the below image.

Power BI SUMIF Equivalent Function: FILTER

The other equivalent of the SUMIF Power BI is the FILTER Function. It is a table-based function that returns a table as output. The FILTER Function for the current example will use the following syntax:

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

The above Power BI SUMIF equivalent FILTER Function uses 2 parameters which are as follows:

• Table: The first parameter is a table that you can input in the form of a complete Table or as a single-column Table with the help of the “All()” function in DAX. The SUMX Function in the above expression works iteratively and requires a Table as the first parameter.
• Filter: The “FILTER(Marks, Marks[Mid term Marks] > 15)” parameter uses the FILTER Function to aggregate midterm marks only for subjects having marks higher than 15.

So first generate an input Table dynamically by clicking the modeling tab at the top and further selecting the “New Table” option. Now, using the FILTER Function, you can generate the following Table: 

Now, using this table as input to the SUMX Function, drag the measure “sumif w filter” onto the report. Using the SUMX Function you will see the following results:

That’s it! You can now try the above 2 Power BI SUMIF equivalent functions on your own.

Benefits of DAX Functions

Power BI itself comes along with incredibly useful features. The previous section highlighted the importance of DAX as it provides Power BI SUMIF equivalent functions. Furthermore, deploying DAX Functions in Power BI will optimize your experience with Power BI and provide you with the following additional benefits: 

• DAX library opens up the world of advanced Power BI features. For example, with DAX, you can perform dynamic data manipulations via select, join, filter, and other similar commands. This implies, users will give input to the Power BI dashboard and it will then dynamically generate output data in the form of Calculated Columns, and Tables.
• DAX helps in making your Power BI Dashboards smart. This is achieved by implementing Calculated Columns and Powe BI Measures to put constraints on the data fetched by your Dashboard. Furthermore, a correct DAX expression can optimize your latency and fulfill data processing tasks using limited resources. 
• DAX uses a collection of nested filter contexts to carry out performance optimization. Therefore, DAX Functions will help you build an optimal thinking pattern for creating tables and filtering data. This implies, your DAX experience will sharpen your thinking and enable you to find better solutions concerning operations like merge, filter, and data manipulation. 

Conclusion

This article introduced you to Power BI and DAX along with their key features. It also discussed the value of the DAX Library for the Power BI platform. It further explained the SUMIF Function and provided a detailed discussion on implementing Power BI SUMIF equivalent functions. The article also listed the benefits of using DAX Functions for your Power BI tasks. After reading this article, you can go and experiment with the Power BI SUMIF equivalent functions and calculate the conditional sum of your datasets seamlessly.

Visit our Website to Explore Hevo

Power BI is a great tool for performing Data Analytics and Visualization for your business data. However, at times, you need to transfer this data from multiple sources to your PowerBI account for analysis. Building an in-house solution for this process could be an expensive and time-consuming task. Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 

Share your views on Power BI SUMIF Equivalent Functions in the comments section!

FAQ on Power BI SUMIF Function

1 -- https://res.cloudinary.com/hevo/image/upload/v1729852677/hevo-blog/ebook-downloadable-papers/ebooks/How_a_Modern_Data_Stack_Creates_a_360_Degree_View_pecmu0.pdf --- Download Your EBook For Free - Exit Intent Popup