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**.

Table of Contents

**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 |

**Simplify Power BI Data Analysis with Hevo’s No-code Data Pipeline**

Hevo is the only real-time ELT No-code Data Pipeline platform that **cost-effectively** automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (**40+ free sources**), we help you not only **export data** from sources & **load data** to the destinations but also transform & enrich your data, & make it **analysis-ready**.

Start for free now!

Get Started with Hevo for Free## 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.

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.