How to Use SUMX Power BI Function?

Power BI is a rapidly growing Business Intelligence (BI) tool and Data Visualization software developed by Microsoft to transform the way Data Analytics is used by organizations to solve business problems. Power BI makes working with data easy with real-time high-level analytics, extensive modeling, and custom development. However, when using Microsoft Power BI, you’ll frequently discover that you must work around various functions to make the most of business data. This blog will take you through one such classical and important function, SUMX Power BI.

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants used to create formulae and expressions in Power BI. DAX SUMX is one such inbuilt mathematical DAX function in Power BI that returns the sum of expression from a table. SUMX Power BI is an iteration function that works on a row-by-row calculation as per the given expression. But before getting started with SUMX Power BI, let’s discuss this robust BI platform in brief.

What is SUMX Power BI Function?

The Power BI Data Analysis Expressions (DAX) comprises over 200 functions, operators, and constants that provide immense flexibility in Data Analysis tasks. SUMX Power BI is a DAX iterator function that forms the fundamental block for tabular models. Being an iterator function, DAX SUMX iterates through every row of a specified table and facilitates the sum of products. This means that the product of the corresponding field values is first performed for each row and the values are then added.

Iterator functions perform row-by-row calculations by considering each row at a time and applying the calculation. Other iterator functions include COUNTX, RANKX, PRODUCTX, etc.

Syntax

A typical SUMX Power BI function looks like this:

SUMX (<Table>, <Expression>)

Let’s break this down and try to understand the 2 parameters.

• Table: This specifies the table name to which the DAX SUMX function is applied. The steps or iterations will be carried out on the rows of this table. To delve deeper into creating tables using DAX, explore creating Power BI new Table using DAX table construction.
• Expression: This is the Expression or Equation that needs to be evaluated row-wise.

A SCALAR return value of any type is returned as the result of the sum.

Example

Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

SUMX will iterate through a sample table “Sales” specified in the first parameter, row by row, and perform the calculation (Quantity*Net Price) specified in the second parameter. After evaluating the calculation for every row in the specified table, it’ll add up all the values.

--  SUM is the short version of SUMX, when used with one column only
--  SUMX is required to evaluate formulas, instead of columns
DEFINE
    MEASURE Sales[# Quantity 1] = SUM ( Sales[Quantity] )
    MEASURE Sales[# Quantity 2] = SUMX ( Sales, Sales[Quantity] )
    MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    "Quantity 1", [# Quantity 1],
    "Quantity 2", [# Quantity 2],
    "Sales Amount", [Sales Amount]
)

The above query will return the following output.

Color	Quantity 1	Quantity 2	Sales Amount
Silver	27,551	27,551	6,798,560.86
Blue	8,859	8,859	2,435,444.62
White	30,543	30,543	5,829,599.91
Red	8,079	8,079	1,110,102.10
Black	33,618	33,618	5,860,066.14
Green	3,020	3,020	1,403,184.38
Orange	2,203	2,203	857,320.28
Pink	4,921	4,921	828,638.54
Yellow	2,665	2,665	89,715.56
Purple	102	102	5,973.84
Brown	2,570	2,570	1,029,508.95
Grey	11,900	11,900	3,509,138.09
Gold	1,393	1,393	361,496.01
Azure	546	546	97,389.89
Silver Grey	959	959	371,908.92
Transparent	1,251	1,251	3,295.89

How To Use SUMX in Power BI?

Applying the SUMX Power BI function is very much straightforward. For the purpose of this demonstration, a sample “Sales_Table” is considered. The dataset contains data pertaining to different cities, the cost price of a product, and the number of units sold. Using the SUMX DAX function, the total sales value can be calculated. To do so, follow the below-mentioned steps.

Here’s the sample dataset.

As you can observe, the table contains 3 columns: City, Price, and Units. You can now upload this dataset to Power BI.

• To do so, go to the “Home” menu and click on the “Get Data” tab.
• Since our data is in Excel, select the “Excel” option.

• Click on “Load” and name the table as “Sales_Table”.
• To arrive at each row calculation, apply the SUMX Power BI function in “New Measure”. Right-click on the table and select “New Measure”.
• Name this new measure as “Sales Value”.
• Open the SUMX function and provide the table name and the expression to be evaluated. The table name is “Sales_Table” and you need to multiply Units with Price to get the Sales Value.
• Close the bracket and press “Enter” to get the result.

• Now, to visualize the results, click on the “Report” tab and insert the “Table” visual in it from the visualization list.

• Drag and drop the “City” and “Sale Value” fields under the “Values” tab to get the summary table.

• This returns the Sales Value for each city as desired.

How is SUMX Power BI Different From SUM Power BI?

Understanding the difference between SUM and SUMX Power BI functions can be a turning point for many Data Professionals. Learning how to leverage these functions appropriately can provide the opportunity to dive deeper into raw data to extract actionable insights with ease. Using them effectively will remove complexities from your Data Analysis and will unleash the full potential of your data. Though both the functions look similar, they’re much different.

Power BI comprises 2 basic Calculation Engines: Aggregator Engine and Iterator Engine. DAX SUM function belongs to the Aggregator Engine and it adds all the values in a single column to return the result. SUM considers a single column as a whole and returns a result. SUM and other aggregator functions are not capable of performing row-wise evaluations.

A typical SUM function looks like this:

SUM(<Column Name>)

Example: Total Units = SUM(Sales_Table[Units])

As discussed, the DAX SUM function considers a single column of data to add all the data in that column. So, the SUM function will add every single value in the “Units” column of Sales_Table to return the total number of units.

SUMX Power BI is an iterator function and takes a different approach. Unlike SUM, SUMX is capable of performing row-by-row calculations and iterates through every row of a specified table to complete the calculation. SUMX then adds all the row-wise results of the iterations of the given expression.

A typical SUMX Power BI function looks like this:

SUMX (<Table>, <Expression>)

Example: Sales_Table[Sales Value] = SUMX ( Sales_Table, Sales_Table[Units] * Sales_Table[Price] )

As discussed, the SUMX Power BI function will iterate through every row to perform the given calculation before adding the row-by-row values. So, the SUMX function will multiply the “Price” and “Units” from every single row and will then add the results to return the total sales amount in the “Sales Value” column.

When to Use Power BI SUMX?

You can use the SUMX Power BI function whenever there is a need for the row-by-row calculation. Hence, if your data is structured in a way that you will necessarily need to multiply values from 2 columns one row at a time in order to get the desired result, you simply must use the SUMX Power BI function.

SUMX can operate over a single column as well, so, you could, in theory, use SUMX as a simple aggregation function to add all the values in a column. However, it is not recommended to use SUMX Power BI as an aggregator due to its iterative nature. SUMX uses more resources than a SUM function and might affect the performance of your dashboard negatively.

When to Use Power BI SUM?

SUM should be used whenever it is just a simple calculation across a single column and row-wise execution is not required. Hence, if your data is structured in a way that it contains only a single column of values, then you can use SUM to add up the values. The DAX SUM function operates over a single column and hence there is no need for an iterator in a case where you are simply trying to calculate the sum of a column of data.

SUMX vs CALCULATE

The CALCULATE function in DAX is a powerful tool that alters the filter context for a calculation. Its syntax is as follows:

CALCULATE(Expression, Filter1, Filter2,...)

CALCULATE is particularly useful when you need to perform calculations under different filtering conditions than those currently applied to the report or model. Conversely, SUMX is designed for row-level calculations followed by aggregation.

In intricate situations, CALCULATE can be combined with other functions such as SUMX. For instance, you may utilize CALCULATE to specify a particular filter context within which SUMX executes its row-level calculations and aggregation.

Mistakes to avoid when utilizing SUMX Power BI

When utilizing Sumx in Power BI, the most frequent mistakes to avoid include improper data type usage, references to invalid columns or tables, and incorrect syntax. It is generally advised to thoroughly understand DAX syntax and functions principles before developing any intricate calculations or measures.

One additional common mistake to avoid when using Power BI with Sumx is to misuse the filter context. It is crucial to comprehend the distinction between row context as well as how each impacts the outcomes of your computations.

The effects of utilizing Sumx in Power BI on performance should also be considered. The function Sumx Power BI may need a lot of resources, particularly when working with huge datasets. For best results and lowest error rate, optimize your data model and make use of additional DAX functions like Calculate and Summarise.

Benefits of using SUMX

• Versatility

SUMx offers versatility in calculations by being compatible with any table or column.

• Dynamic Calculations

SUMX lets you compute particular values by applying conditions or filters.

• Advanced Analysis

You can carry more intricate computations and acquire a more profound understanding of your data by combining SUMX with other DAX functions.

The Perks of Sumx Over Other Power BI Aggregation Functions

Sumx Power BI is a more flexible way to work with complex data than existing aggregation functions in Power BI. It can calculate multiple values depending on table expressions and execute computations over filters.

Recommendation for Maximizing Performance While Utilizing Power BI’s Sumx

Take into consideration the following advice to maximize performance when utilizing Sumx Power BI:

• Reduce the amount of times you use the Sumx function and, if you can, make it simpler.
• Limit the extent of the table of columns where the data is being aggregated.
• To avoid laborious data processing, use computation groups and proactive caching.
• Reduce the amount of data you’re processing by using appropriate data modeling. 

Using DAX to Improve Power BI’s Sumx Computations

Working with SumX requires the use of DAX because it gives you access to more than 200 functions, allowing you to develop even more complex calculations. DAX can be used to build custom computations and increase Sumx’s functionality.

Read: Power BI DAX Count Function: An Ultimate Guide 101

Tips and Tricks for Using SUMX in Power BI

First Tip: For complex calculations, use DAX variables.

• Using DAX variables in Power BI can simplify complicated calculations:
• Determine which calculation has to be done.
• Use the VAR keyword to declare a variable, then give it a name.
• Use the “=” to assign the result of computation or expression to the variable.
• In the intended calculation or formula, utilize the variable in place of the original expression.

With the use of DAX variables, you can:

• Boost the readability and maintainability of formulas.
• Utilize calculation more than once.
• Divide complicated computations into more manageable chunks.

Tip 2: For more Advanced Analysis, combine SUMX with other DAX functions

You can use the SuMX function in conjunction with other DAX functions to improve Power BI’s Analytical capabilities. Follow these steps to get started:

• Choose the DAX functions that best suit your needs for analysis.
• To calculate the desired result, iterate over a table or column using the SUMX function.
• To generate more complex calculations, combine the SUMX function with additional DAX functions like AVERAGE or COUNTX.

Conclusion

Power BI is Microsoft’s Business Intelligence tool used by Data Professionals worldwide to examine data from multiple sources and create attractive Charts, Dashboards, and Reports according to user-specified data. Making reports using the Power BI functionalities of data importing, transforming, and visualizing is a smooth experience. However, one must be familiar with important DAX functions such as SUMX Power BI to get the most out of raw data.

This blog introduced you to Power BI and took you through various aspects of the SUMX Power BI function. The article also differentiates between Power BI SUM and SUMX functions before concluding. Power BI makes Business Analysis more efficient through intuitive, interactive, and easy-to-use services. Moreover, analyzing and visualizing your data by loading it from a Data Warehouse to Power BI can be cumbersome. This is where Hevo comes in.

visit our website to explore hevo

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo pricing offers different use cases and business needs, check them out!

Share your experience of understanding the SUMX Power BI function in the comments section below.

References:

• SUMX DAX Function (Aggregation)