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>)
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.
Lets consider this example: Total Units = SUM(Sales_Table[Units])
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 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 function looks like this:
SUMX (<Table>, <Expression>)
You can use the SUMX 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.
