Power BI – Difference between SUM() and SUMX()

Recognizing that Power BI has two fundamental computation engines will help you move forward. An iterator engine and an aggregator engine both exist. The distinction between SUM and SUMX in Power BI is still very unclear. Both functions can be utilized in various contexts, but there are some situations when one is more effective than the other, thus users need to be aware of this crucial information.

Aggregators Vs Iterators

Aggregators and iterators are the two different categories of computation engines used in DAX. The aggregating operations SUM, AVERAGE, MIN, MAX, and COUNT are examples. On the other hand, iterators are functions like SUMX that have an X at the end.

In order to apply logic to each row of a table, iterating functions run over every single row in the table. When the context is included in a calculation, aggregating functions examine the entire column that remains. Following that, a single aggregate is performed for the entire column at once.

Sum

The Power bi sum function will total all the numbers in a column, where the column includes the values to sum. It gives back a decimal number. 

SUM Belongs to Aggregator Engine

The ideal illustration of a function that belongs to the aggregator engine is SUM. Every value in a single column is added (or aggregated) together, and the outcome is returned. SUM can only add all of the values in the column it is applied to. SUM and other aggregator functions are unable to do row-by-row analyses because they lack visibility into the idea of a row (Other aggregator functions include COUNT, AVERAGE, MAX, MIN, etc.)

You may slice any measure by any dimension with which the measure has a relationship in the model by using the dataset in the example below, which includes a Sales column in the Orders table that we’ll apply SUM on to generate the measure Total Sales and then show by the Category dimension.

Syntax: SUM(<column>)

Dataset

A dataset is a group of data that you connect to or import. Power BI enables you to connect to, import, and combine many datasets in one location. Additionally, dataflows can provide data to datasets. workspaces and datasets are related, and a single dataset might be a component of numerous workspaces.

In this we have used the Sales Dataset from Amazon retail sales, this dataset contains columns such as ProductID, Category, subcategory, Product Name, Quantity, Sales, etc., and this dataset contains 9000 rows approximately. The source of this dataset is taken from the Excel workbook. Look at the picture below to know about the dataset used.

Let’s see with an example by creating a measure using SUM ( ).

Example: Total Sales=SUM(Orders[sales])

To get this measure Query, go to the data tab>>select your required table>>select table tools from the menu>>select new measure>>it opens DAX query window>> then write the above query>> press enter, It creates new measure, you can see this in the fields section.

A new field is added as total sales.

To visualize the data >> select the Report tab on the left side of the page>>go to the visualization section>> select card visualization>>go to the fields section >>expand orders table>>Drag and drop Total sales.

For the second visualization,>>  go to the visualization section >> select Table visualization>> drag and drop Category and total sales. For the third visualization >>  go to the visualization section >> select Stacked column visualization>>drag and drop Category and total sales.

Note: Measures should always be kept in a separate measure table. The above picture shows Total Sales by category.

SUMX

The SUMX function can be used to define an expression to sum over in order to filter the values that are being summed.

SUMX Is a Member of Iterator Engine

SUMX is an iterator function, which implies that after finishing the evaluation, the function develops another piece of logic by iterating through each row of the provided table. Iterator engine functions can execute row-by-row calculations because, unlike aggregators, they are aware of the rows in a table (other iterator functions include COUNTX, RANKX, PRODUCTX, etc.)

Let’s construct a measure for the total sales after any discounts are applied using the same dataset. The formula to create a Sales Less Discounts measure is SUMX. Using the syntax listed above for the SUMX function, this can be done by using the SUMX function to iterate through each row of the data and then applying that logic.

Let’s see with an example by creating a measure using SUMX( ). Here we will see how to sum multiple columns using SUMX ( ).

Syntax: SUMX (<table>, <expression>)

Example: Sales Less Discounts=SUMX(Orders, Orders[Sales]-Orders[Discounts])

To get this measure Query, go to the data tab>>select your required table>>select table tools from the menu>>select new measure>>it opens DAX query window>> then write the above query>> press enter, It creates new measure, you can see this in the fields section. Here, we select the Sales and Discounts columns from Orders Table.

To visualize the data >> select the Report tab on the left side of the page>>go to the visualization section>> select Stacked column chart visualization>>go to the fields section >>expand orders table>>Drag and drop Category and Slaesless discount measure.

We can see from the snapshot above that the Sales Less Discounts formula appropriately subtracts the Discount column from the Total Sales column by looking at the chart in the top right corner. In essence, the SUMX function subtracts the Discount column from a row after going through each row of the Sales column.

Note: Use SUMX ONLY when a row-by-row calculation is required. Theoretically, SUMX may be used for a specific aggregation to calculate the sum of a column. Still, because iterative SUMX consumes more resources than a straightforward SUM function, this is not recommended. In order to avoid having your dashboard perform poorly, avoid using SUMX (or any other iterator function) to perform straightforward aggregations of a column.

Significance of SUM and SUMX

Many Power BI developers can experience a paradigm shift once they comprehend the distinction between SUM and SUMX as well as when and how to utilize them properly. This is because it can provide you the chance to easily go deeper into your data by enabling you to remove redundant, unnecessary processes that can lead to the same conclusion that you could have reached with a single function. This knowledge can serve as a starting point for learning additional, more complicated aggregator and iterator routines.

Conclusion

• SUM and Aggregator Functions: A single column is combined into one value via aggregator functions. After all context filters have been applied, SUM collects a single column and creates a single summing of the column.
• SUMX is an Iterator Function: Row-by-row iteration and evaluation are completed by iterator functions. After applying all context filters, SUMX iterates through each row of the table, aggregates the data, and then applies one more piece of logic before generating the final summation.