SUM vs SUMX in DAX: Understanding Row-by-Row Calculations

September 28, 2026 94 visits

In this article, we will learn the difference between SUM and SUMX in DAX using a simple example. We will also see how COALESCE can be used inside SUMX to replace blank values with a default value.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist

What Are SUM and SUMX in DAX?

Both SUM and SUMX are aggregation functions, but they work differently.
SUM directly adds the values from a single column, while SUMX evaluates an expression for every row and then adds the resulting values.

Function	How it works
SUM	Adds the values of a column.
SUMX	Evaluates an expression row by row and then sums the results.

Key takeaway:
SUM works directly on a column, whereas SUMX works on a table and an expression evaluated for each row.

1. Retrieving the CountTable

Query

DAX Query

EVALUATE
    CountTable

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return a table as the result of a DAX query.

Step 2: CountTable
CountTable is the table expression being returned. DAX returns the rows and columns available in this table.

What will you get?

The result displays the complete CountTable, including columns such as Column A and Column B.

Key takeaway:
This first query is useful for inspecting the source data before applying aggregation functions.

2. Calculating a Column Total Using SUM

Query

DAX Query

EVALUATE
    ROW(
        "Sum", SUM(CountTable[Column A]),
        "Sum 01", SUM(CountTable[Column B])
    )

Step-by-step explanation

Step 1: ROW
ROW creates a single-row table containing the calculated results.
Here, two output columns are created: Sum and Sum 01.

Step 2: SUM(CountTable[Column A])
The SUM function takes Column A and adds its values together.

Step 3: SUM(CountTable[Column B])
The second SUM performs the same operation on Column B.
Blank values are not converted into another number; they are simply ignored by the aggregation.

Understanding the SUM syntax

SUM Syntax

SUM(<Column>)

SUM expects a column and returns the total of the values in that column.
It does not provide an expression for each individual row.

Key takeaway:
Use SUM when you simply need to add the values of an existing numeric column.

3. Using SUMX for a Row-by-Row Calculation

Query

DAX Query

EVALUATE
    ROW(
        "Sum X", SUMX(CountTable, COALESCE([Column B], 2))
    )

Step-by-step explanation

Step 1: SUMX
SUMX takes a table as its first argument and an expression as its second argument.
It evaluates that expression for every row of the table and then adds the results.

Step 2: CountTable
CountTable is the table that SUMX iterates through row by row.

Step 3: COALESCE([Column B], 2)
For each row, COALESCE checks the value of Column B.
If the value is available, that value is returned. If the value is BLANK, 2 is returned instead.

Step 4: SUMX adds the evaluated values
After the expression has been evaluated for every row, SUMX adds all those resulting values together.

Understanding the COALESCE logic

COALESCE Expression

COALESCE([Column B], 2)

The logic can be understood as:

Column B value	Expression result
A numeric value	The original numeric value
BLANK	2

Key takeaway:
SUMX allows you to transform or replace values row by row before calculating the final total.

4. SUM vs SUMX with COALESCE

This is the important difference demonstrated by the query. The following two expressions both work with Column B, but they do not necessarily produce the same result.

Expression	Behavior
SUM(CountTable[Column B])	Adds the existing values in Column B. Blank values are ignored.
SUMX(CountTable, COALESCE([Column B], 2))	Checks every row, replaces BLANK with 2, and then adds the resulting values.

For example, if Column B contains values such as 10, BLANK, 20, then:

Function	Calculation concept	Result
SUM	10 + 20	30
SUMX + COALESCE	10 + 2 + 20	32

This example shows why SUMX is useful when the value being aggregated needs to be modified before it is added.

5. Understanding the Complete Query

Complete Query

DAX Query

// Sum And SumX

EVALUATE
    CountTable

EVALUATE
    ROW(
        "Sum", SUM(CountTable[Column A]),
        "Sum 01", SUM(CountTable[Column B]),
        "Sum X", SUMX(CountTable, COALESCE([Column B], 2))
    )

Step-by-step explanation

First EVALUATE: Returns the complete CountTable so that the source data can be inspected.
ROW: Creates a single-row result containing three calculated values.
Sum: Uses SUM to total Column A.
Sum 01: Uses SUM to total Column B.
Sum X: Uses SUMX to iterate through CountTable, replace blanks in Column B with 2, and then calculate the total.

Result structure

Output Column	Expression	Purpose
Sum	SUM(CountTable[Column A])	Total of Column A
Sum 01	SUM(CountTable[Column B])	Total of Column B
Sum X	SUMX(CountTable, COALESCE([Column B], 2))	Total of Column B after replacing blanks with 2

6. When Should You Use SUM and SUMX?

Requirement	Recommended function
Simply total an existing numeric column	SUM
Apply an expression to every row before totaling	SUMX
Replace blanks before calculating the total	SUMX + COALESCE
Perform multiplication or other calculations per row before summing	SUMX

Important:
Do not choose SUMX simply because it can produce a total. Use it when you actually need row-by-row expression evaluation.

📝 Practice Exercise

Using the CountTable, try creating a DAX query that calculates:

The total of Column A using SUM.
The total of Column B using SUM.
The total of Column B using SUMX, replacing every blank value with 5.

Then compare the two totals for Column B. If the column contains blank values, observe how replacing those blanks changes the result.

Conclusion

In this article, we learned how SUM and SUMX behave differently in DAX.

SUM directly adds the values from a column.
SUMX evaluates an expression for every row and then adds the results.
COALESCE can be used inside SUMX to replace blank values with a specified value.
SUMX(CountTable, COALESCE([Column B], 2)) replaces each blank in Column B with 2 before calculating the total.
The choice between SUM and SUMX depends on whether a simple column aggregation or a row-by-row calculation is required.

Understanding this difference is important because many real-world DAX calculations require you to transform values at the row level before aggregating them.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist