For many Power BI developers, the difference between the DAX functions SUM and SUMX can be a confusing topic. Though they are similar, there is a big difference, and if used effectively, they will unlock a whole new layer of potential in both your analyses and dashboard development. From a high level, this blog covers the differences between the SUM and SUMX DAX functions, when to use them, and some examples.
What are Power BI’s SUM and SUMX?
It’s helpful to take a step back and understand that Power BI has two basic calculation engines. There is an aggregator engine and an iterator engine.
SUM Belongs to the Aggregator Engine
SUM is a perfect example of a function that is a member of the aggregator engine. It adds (or aggregates) every value in a single column and returns the result. The only thing that SUM is capable of doing is adding all of the values of the column it is used on. SUM, and other aggregator functions, have no visibility into the concept of what a row is and are not capable of performing row-by-row evaluations (other aggregator functions include COUNT, AVERAGE, MAX, MIN, etc.).
The syntax for SUM is: SUM( )
The dataset in the example below has a Sales column in the Orders table that we’ll use SUM on to create the measure Total Sales, and then display it by the Category dimension – you can slice any measure by any dimension that the measure shares a relationship within the model.
Using the syntax above for SUM, the formula to achieve this is:Â
Total Sales = SUM(Orders[Sales])
Data view showing the Sales and Category columns in the Orders table
Note: I always keep my measures in a separate Measure Table (in the screenshot below) – read about that best practice in this Creating a Measures Table in Power BI blog post.
Report view showing Total Sales by Category
SUMX Belongs to the Iterator Engine
The fact that SUMX is an iterator function means that it iterates through every row of a specified table to complete the evaluation and then works out an additional piece of logic within the function. Unlike aggregators, functions belonging to the iterator engine are aware of rows in a table and thus can perform row-by-row calculations (other iterator functions include COUNTX, RANKX, PRODUCTX, etc.)
The syntax for SUMX is: SUMX(, )
Using the same dataset, let’s create a measure for the Total Sales after any discounts are applied. We can use the SUMX function to iterate through each row of the data and apply that logic to create a Sales Less Discounts measure.Using the syntax above for SUMX, the formula to achieve this is: SUMX(Orders, Orders[Sales] – Orders[Discount])
Report view showing Total Sales, Discounts, and Sales Less Discounts by Category
Looking at the chart on the top right in the screenshot above, we can confirm that the Sales Less Discounts is correctly subtracting the Discount column from the Total Sales column. What the SUMX function is effectively doing is going through each row of the Sales column and subtracting the Discount column from the same row.
Pro Tip: ONLY use SUMX when there is a need for the row-by-row calculation. You could, in theory, use SUMX for a simple aggregation to get the summation of a column; however, due to the iterative nature of SUMX, it uses more resources than a simple SUM function. Therefore, do not use SUMX (or any other iterator function) to perform simple aggregations of a column because it will negatively affect the performance of your dashboard.
Why are SUM and SUMX Important?
Understanding the difference between SUM and SUMX and when and how to leverage them appropriately can be a turning point for many Power BI developers. This is because it can provide the opportunity to, with ease, dive deeper into your data by allowing you to remove extra overly-complex steps that may get you to the same answer you could have gotten to with a single function. This understanding can also be the foundation for gaining experience with other, more complex aggregator and iterator functions.
Closing
To summarize:
SUM and Aggregator Functions
- Aggregator functions aggregate a single column into a single value.
- SUM aggregates a single column and produces a single summation of the column after applying all context filters.
SUMX is an Iterator Function
- Iterator functions perform a row-by-row iteration and complete an evaluation.
- SUMX iterates through each row of the table and then aggregates the values after applying all context filters, then applies an additional piece of logic before producing the final summation.Â
