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.
A fully-managed No-code Data Pipeline platform like Hevo helps you integrate and load data from 150+ different sources to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance.
Why Choose Hevo?
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
Hevo can help you scale your data infrastructure as required.
Get Started with Hevo for Free
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.
Master the RANKX function in Power BI to rank your data efficiently and enhance your reports. Learn more at RANKX Power BI.
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] )
- This code defines a calculated measure named
Sales Amount.
- It uses the
SUMX function, which iterates over a table—in this case, the Sales table.
- For each row in the
Sales table, it multiplies the Quantity by the Net Price.
- The results of these multiplications are then summed together to give the total sales amount.
- This calculation provides a way to compute the total revenue generated from sales transactions.
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]
)
- This code defines three measures in a data model:
# Quantity 1, # Quantity 2, and Sales Amount.
# Quantity 1 uses the SUM function to total the Quantity column, while # Quantity 2 uses SUMX to iterate over the Sales table and sum the Quantity.Sales Amount calculates the total revenue by multiplying Quantity and Net Price for each row in the Sales table and then summing the results.- The
EVALUATE statement retrieves summarized data by grouping it by the Color column from the Product table.
- The result displays the total quantities and sales amounts for each product color in a structured format.
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
SUMx offers versatility in calculations by being compatible with any table or column.
SUMX lets you compute particular values by applying conditions or filters.
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.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Share your experience of understanding the SUMX Power BI function in the comments section below.
References:
Frequently Asked Questions
1. What is the sumx function in Power BI?
SUMX in Power BI is a DAX function that sums the results of an expression evaluated row by row across a table.
2. What are DAX formulas?
DAX formulas are used in Power BI to create complex calculations and data models, leveraging functions, operators, and expressions.
3. Which is faster sum or SUMX?
SUM is generally faster than SUMX because it performs a straightforward aggregation without iterating over rows, whereas SUMX is necessary for more complex row-by-row calculations.
Raj, a data analyst with a knack for storytelling, empowers businesses with actionable insights. His experience, from Research Analyst at Hevo to Senior Executive at Disney+ Hotstar, translates complex marketing data into strategies that drive growth. Raj's Master's degree in Design Engineering fuels his problem-solving approach to data analysis.
