The SUM function is one of, if not the most widely used method of aggregation in Power BI DAX. SUMX is a related function that’s less widely used but can be equally important when building out calculations. We’ll explore the different use cases, explain how they work, and when to use each one.
SUM and SUMX are both aggregation functions in DAX. They are meant to add each row value in a column of data, typically when creating a new measure, but they apply the calculation in different ways.
Let’s dive in!
Table of Contents
The DAX SUM Function
The SUM function in Power BI DAX is one of the most straightforward and widely used functions. It is designed to calculate the sum of a column in a table. It’s fast, easy and widely used
SUM ( <ColumnName> )
For instance, if you have a table named
CookieSales and want to calculate the total amount of sales, you would use:
SUM ( CookieSales[Amount] )
Tip: If you’re new to Power BI, you can begin typing a column name in the DAX Formula bar and press tab to allow Power BI to auto complete the formula which helps you avoid typing the full word with correct punctuation. You can only SUM and SUMX using a Measure. You cannot SUM when creating a new column.
The DAX SUMX Function
SUMX is an iterator that evaluates an expression for each row of a table and then sums the results. SUMX allows for row-by-row operations before summing the results, which is something SUM cannot do.
SUMX ( <Table>, <Expression> )
Use Case – Fixed Values
Suppose you want to calculate the total sales amount in the
CookieSales table, but you need to apply a specific condition or modify the amount on each row before summing. For example, if you want to add a 10% tax to each sale before calculating the total sum, you would use:
SUMX ( CookieSales, CookieSales[Amount] * 1.10 )
In this scenario, SUMX multiplies the
Amount in each row by 1.10 and then sums these modified values.
Use Case – Multiply Across Columns
Use SUMX when the calculation involves modifying each row’s data first. For example, applying different tax rates based on conditions or calculating weighted sums.
SUMX ( CookieSales, CookieSales[Amount] * CookieSales[TaxRate] )
Comparison of SUM and SUMX
In Power BI DAX the SUM function is used to add all of the values in a column together. SUMX is used to apply an additional operation to each row, and then adds all of the modified values together. For example, SUMX could be used to multiply a dollar amount by a tax rate that varies in each row of data prior to adding the resulting values.
The following example illustrates how SUM and SUMX work under different scenarios.
Scenario 1.) SUM then Multiply
This scenario is most common. You total the values in a column, then multiply it by a fixed amount.
Scenario 2.) SUMX with Fixed Values
This scenario evaluates each row one at a time. It takes the value in a row, multiplies it by a fixed number, then adds the results at the end. In this case, the result is the same as if you were to SUM then multiply by a fixed number.
Scenario 3.) SUMX with Dynamic Values
SUMX shines when applying operations with different values for each row of data. For example, if each row represented sales by state with a different tax rate for each state, you could use SUMX to multiple each value by a different multiplier, then total the results.
SUMX is most often used with multiplication but can be used with other operators.
Difference between Power BI SUM and SUMX
In Power BI, use SUM to calculate the total amount in a column. Use SUMX to multiply each row of a column by a value in another column prior to adding all of the resulting values.
Another way to think of it is that SUMX can be used to SUM, but SUM cannot be used to SUMX. SUMX in a way is the same as SUM “X” or SUM times the value in another column.
Power BI SUMX vs Calculated Columns
SUMX and Calculated Columns perform two different functions in Power BI. A Calculated Column is a new column added to a table in Power BI, where each row’s value is calculated on a row-by-row basis. SUMX performs row-by-row operations and then sum these results. SUMX will automatically aggregate values, a calculated column will not.
If you did not want to use a SUMX formula, you could instead use a calculated column to multiply two columns together, then use a regular SUM formula to add the results of the calculated column together. SUMX allows you to combine these two steps into a single function.
Even though the results would be the same when added to a visual, there are some differences in the way that Power BI executes DAX measures vs creating columns in your dataset, primarily the time at which each query executes during the loading and report exploration process.
The SUM function in Power BI is used to add a column of values together and provide the result. SUMX is used to perform an operation on a row by row basis prior to adding the results together. For example, multiplying values from two different columns together one row at a time then adding the results together. Such as taking Sales Quantity x Sales Price then adding the result together to get total sales where each row might represent an item that has a different sales price.
Calculated Columns could also be used to multiply the values of multiple columns together, and when combined with SUM re-create the results of SUMX. However, SUMX is often a more straightforward way to perform the operation and reduces the number of steps needed for a calculation.