How to use Power BI SUMIF Function?

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.

Why are DAX Functions Important?

• Advanced Analytics: Enable complex calculations and custom aggregations beyond standard capabilities.
• Dynamic Calculations: Allow for the creation of measures and calculated columns that adapt to filters and slicers in reports.
• Custom Data Modeling: Facilitate tailored solutions to meet unique business requirements.
• Optimized Performance: Improve query efficiency for large datasets through optimized formulas.
• Enhanced Interactivity: Enable dynamic insights by responding to user interactions in real-time.

To learn more about the DAX function in Power BI, check out our blog.

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

Mastering SUMIF-like functionalities in Power BI enables users to create dynamic and efficient conditional calculations, making data analysis more streamlined and insightful. Using DAX functions like CALCULATE and SUM, you can replicate the behavior of traditional SUMIF operations to derive meaningful insights from your datasets.

Power BI’s robust capabilities allow you to explore and visualize data effectively, making it a powerful tool for decision-making. By continuing to learn and experiment with DAX functions, you can unlock the full potential of Power BI for your business and analytical needs. As you explore Power BI further, migrating data from various sources to your desired destination could be a big step in this process. Integrating your sources with a reliable data pipeline solution like Hevo can enhance your workflows, ensuring seamless data integration and real-time updates. Try Hevo to experience a streamlined and efficient data analysis process.

FAQ on Power BI SUMIF Function