SUM() vs SUMX(): What is the difference between the two DAX functions?

[Anonymous]

SUM() vs SUMX(): What is the difference between the two DAX functions?

[FreemanZ · Accepted solution]

hi @Anonymous 

SUM is syntax sugar, runned as SUMX in the background. 

[Sahir_Maharaj · Accepted solution]

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.

[Sahir_Maharaj · Accepted solution]

The Difference Between SUM vs SUMX In Power BI: https://blog.enterprisedna.co/difference-between-sum-vs-sumx-in-power-bi/

[Sahir_Maharaj · Accepted solution]

SUM Vs SUMX: What's The Difference Between These Two: https://www.youtube.com/watch?v=2Pu0z4GSs7g

[Sahir_Maharaj · Accepted solution]

DAX Beginners - SUM Vs SUMX in Power BI: https://www.youtube.com/watch?v=bQWwj9w06K4

[Sahir_Maharaj · Accepted solution]

Hope this helps you 🙂

[Michaelxq]

The difference between sumx and sum in an example

"CC Consumables Total 1" it's a sumx measure where it firstly multiplies per row and then sums -- (20*79.51) + (-20*-79.51) = 1590.2 + 1590.2 = 3180.4

whereas "Multiply sum" it firstly sums the column and then it multiplies -- (20+(-20))*(79.51+(-79.51)) = 0+0=0

[Sahir_Maharaj · Accepted solution]

Hope this helps you 🙂

[Sahir_Maharaj · Accepted solution]

DAX Beginners - SUM Vs SUMX in Power BI: https://www.youtube.com/watch?v=bQWwj9w06K4

[Sahir_Maharaj · Accepted solution]

SUM Vs SUMX: What's The Difference Between These Two: https://www.youtube.com/watch?v=2Pu0z4GSs7g

[Sahir_Maharaj · Accepted solution]

The Difference Between SUM vs SUMX In Power BI: https://blog.enterprisedna.co/difference-between-sum-vs-sumx-in-power-bi/

[Sahir_Maharaj · Accepted solution]

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.

[FreemanZ · Accepted solution]

hi @Anonymous 

SUM is syntax sugar, runned as SUMX in the background. 

[Anonymous]

Thanks - This is what I needed @FreemanZ 

[johnmelbourne · Accepted solution]

Everythinhg that has an X and a nonX version is just syntax sugar. It is all X iterators.