Introduction
The SUMX function is one of the most powerful aggregation tools in DAX, but also one of the most misunderstood by beginners. Unlike SUM, which works directly on a column, SUMX evaluates an expression for each row in a table and then sums those results.
Think of SUMX as a calculator that goes row-by-row before adding everything together.
What is the SUMX Function in DAX?
Definition:
SUMX is an iterator function — it iterates through each row in a table, evaluates an expression for that row, and then sums up the results.
Syntax:
SUMX(<table>, <expression>)- <table> → The table to iterate over.
- <expression> → The computation that must be done for every row.
How SUMX Works
While SUM only works on an existing numeric column, SUMX lets you calculate something dynamically for each row and then sum it up. This makes it perfect for scenarios where the value you want to sum is not stored directly in your dataset but needs to be calculated.
Real-Life Business Example 1: Sales Revenue from Quantity and Price
Scenario:
Your dataset has Quantity and Unit Price, but no Sales column. You want to calculate total revenue.
Data Table: SalesData
| OrderID | Product | Quantity | Unit Price |
| 101 | Laptop | 2 | 500 |
| 102 | Mouse | 5 | 20 |
| 103 | Monitor | 3 | 150 |
DAX Formula:
Total Sales = SUMX(SalesData, SalesData[Quantity] * SalesData[Unit Price])How it works:
- Row 1: 2 * 500 = 1000
- Row 2: 5 * 20 = 100
- Row 3: 3 * 150 = 450
- Final sum: 1000 + 100 + 450 = 1550
Real-Life Business Example 2: Conditional Sums
Scenario:
You want the total sales only for orders above ₹500.
DAX Formula:
High Value Sales = SUMX(
FILTER(SalesData, SalesData[Quantity] * SalesData[Unit Price] > 500),
SalesData[Quantity] * SalesData[Unit Price]
)Explanation:
- FILTER() selects only rows where sales > 500.
- SUMX() then calculates revenue for those rows and sums it.
SUM vs SUMX – Key Differences
| Feature | SUM | SUMX |
| Works on existing column | ✅ Yes | ❌ No |
| Can calculate before summing | ❌ No | ✅ Yes |
| Performance | Faster | Slower for large data |
| Use case | Simple totals | Dynamic row-by-row totals |
Example:
If your table already has a “Sales” column, use SUM.
If you need to calculate “Sales” from Quantity * Price, use SUMX.
Performance Tips
- Use SUM when possible for speed.
- For large datasets, avoid complex expressions inside SUMX — pre-calculate columns if necessary.
- Combine SUMX with CALCULATE and FILTER for advanced scenarios.
Common Mistakes
❌ Using SUMX when SUM is enough — this can slow down your reports.
❌ Forgetting that SUMX respects filter context — results may change depending on slicers.
❌ Placing too many nested functions inside SUMX without optimizing.
Conclusion
The SUMX function is your go-to when the value you want to sum doesn’t already exist in your dataset. It offers flexibility by calculating on the fly, making it essential for financial analysis, sales tracking, and advanced KPIs.
However, remember that with great power comes great responsibility — use SUMX only when you need dynamic calculations.
FAQs
- Is SUMX slower than SUM?
Yes, because it evaluates each row individually before summing. - Does SUMX work on text columns?
No, only numeric expressions are supported. - Can SUMX be used with measures?
Yes, as long as the measure returns numeric values. - Can I filter inside SUMX?
Yes, by using the FILTER() function as the table argument. - When should I replace SUMX with SUM?
When you already have the calculated column in your table.
