DAX SUMPRODUCT: Multiply and Sum Values in Power BI

DAX does not have a built-in SUMPRODUCT function. To multiply values row by row and add the results, use SUMX with a table and a multiplication expression; for example, a sales measure can multiply each row’s quantity by its unit price and sum those amounts.

What SUMPRODUCT Means in DAX

Excel’s SUMPRODUCT multiplies corresponding entries in one or more arrays, then adds the products. DAX achieves the same central calculation with an iterator: SUMX. It takes a table as its first argument, evaluates an expression for each row of that table, and sums the resulting values.

This is a translation of the idea, not a function-name substitution. DAX is designed to calculate over tables in a data model, where relationships and filters affect which rows are visible. A DAX measure using SUMX therefore responds to a report’s current filter context, such as a selected month, product, or region.

For example, a conventional DAX pattern for a row-by-row multiplication is SUMX(TableName, TableName[FirstValue] * TableName[SecondValue]). Replace the example names with an actual table and its columns. The multiplication is evaluated within each row; SUMX then adds those row results.

How SUMX Works

SUMX is an iterator function. Its first argument identifies the rows to visit, and its second argument is an expression to evaluate for each row. That expression may use columns from the current row, perform arithmetic, or call other functions. The result is a single scalar total, which makes SUMX useful in measures.

It helps to picture the calculation in two stages:

For every row in the table being iterated, calculate the expression. For a sales line, that might be quantity multiplied by unit price.
Add the row-level results together and return the total for the current report context.

This row-by-row behavior is why SUMX can express a product-and-sum calculation that SUM cannot. SUM accepts one column to total; it does not accept a multiplication expression between two columns. When a calculation must derive a value for each row before adding it, an iterator such as SUMX is the appropriate pattern.

A Basic DAX SUMPRODUCT Example

Assume a table named Sales has a row for each order line, with numeric columns Quantity and Unit Price. If the rows are (2, 10), (3, 8), and (4, 5), the line amounts are 20, 24, and 20. Their total is 64.

With those columns in the Sales table, define this measure:

Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

It evaluates 2 × 10, 3 × 8, and 4 × 5, then returns 64 for the full table. In a report filtered to just the second order line, it returns 24. In an actual model, a product, date, or customer filter can change which sales rows contribute, provided the model’s relationships and filter directions allow that filter to reach Sales.

Measures are usually the natural place for this calculation because they recalculate as report filters change. The measure name before the equals sign is part of the DAX measure definition; it is not an Excel worksheet formula.

Adding Conditions to the Calculation

Many Excel SUMPRODUCT formulas include conditions, such as adding products only when a category or status matches. In DAX, a measure can apply a filter with CALCULATE. Assume a Products table has a Category column and is related to Sales through the product key. To total sales for the Accessories category, use:

Accessory Sales = CALCULATE([Total Sales], Products[Category] = "Accessories")

This measure filters the product category and evaluates Total Sales under that filter. The relationship is important: if the product table cannot filter the sales table in the model, the category condition will not select the intended sales lines. A missing or inactive relationship is a modeling issue, not a reason to change the multiplication expression.

For a condition on the rows being multiplied, you can instead pass a filtered table to SUMX. Assume the same Sales columns as above, and that only rows with a quantity greater than one should count:

Sales for Multi-Unit Lines = SUMX(FILTER(Sales, Sales[Quantity] > 1), Sales[Quantity] * Sales[Unit Price])

FILTER returns the qualifying rows, and SUMX multiplies and totals those rows. With the three example lines, the first line is excluded and the result is 44 (24 + 20). Use a condition that reflects the intended business rule; a row filter and a product-category filter are not interchangeable.

Weighted Averages and More Than Two Values

A common reason to look for a DAX version of SUMPRODUCT is a weighted average. The numerator is the sum of each value multiplied by its weight; the denominator is the sum of the weights. Assume a Ratings table has numeric Score and Weight columns. With scores and weights of 80 and 2, 90 and 3, and 70 and 1, define:

Weighted Score = DIVIDE(SUMX(Ratings, Ratings[Score] * Ratings[Weight]), SUM(Ratings[Weight]))

The numerator is 160 + 270 + 70 = 500, and the total weight is 6, so the result is 83.333… (typically formatted as 83.33). DIVIDE is useful because it handles a zero denominator safely by returning blank by default, rather than raising a division-by-zero error. If a different result is required when the denominator is zero, DIVIDE can take an alternate-result argument.

Some Excel formulas multiply three or more corresponding arrays. The DAX expression inside SUMX can also multiply multiple columns from the current row. For example, a line amount could incorporate quantity, unit price, and a discount factor. Check that the discount is represented consistently: multiplying by 0.9 means retaining 90% of the price, while multiplying by 10% calculates only the discount portion.

Filter Context, Totals, and Relationships

A DAX measure does not necessarily calculate once for the entire dataset. Power BI evaluates it separately in each visual cell’s filter context. A table visual grouped by month can show a monthly result on each row, while its total is calculated again for all rows in the total’s context. That total may differ from adding the visible row values when the measure is non-additive, as with averages and ratios.

For a simple sum of line amounts, the grand total will generally equal the sum of the underlying line amounts. For a weighted average, the grand total is recalculated from the combined numerator and denominator; it is not usually the simple average of the displayed monthly averages. This is expected behavior, not evidence that SUMX has ignored the rows.

Relationships determine how filters from date, product, and other dimension tables affect the table iterated by SUMX. A well-structured model commonly has dimension tables related to a fact table such as Sales. If a slicer appears to have no effect, inspect the relationship, the selected field, and the measure’s filter context before rewriting the expression.

Measure or Calculated Column?

For a total that should respond to report selections, create a measure. A measure calculates when needed and returns a result for the current filter context. The Total Sales measure above can therefore show totals by month, region, or product without storing a separate total in every row.

A calculated column instead stores a result for each row when the model is refreshed. A row-level amount column can be useful if other calculations or analyses need that value directly, but it consumes model storage and does not itself provide an aggregated total. You could then sum the column with a separate measure. Avoid creating a calculated column solely to imitate SUMPRODUCT if a measure with SUMX directly expresses the intended calculation.

In Power BI Desktop, create a measure from the Modeling ribbon or by right-clicking a table in the Fields pane and choosing New measure. The exact interface can vary slightly by release. In Excel’s Data Model or Power Pivot, DAX measures are also available, though the menus differ from Power BI. In either case, the model’s table and column names must match the names used in the expression.

Common Errors and Performance Considerations

If a formula returns an error, first check that the referenced columns exist, have compatible numeric data types, and are written with the right table names. Text values in a quantity or price column can prevent the multiplication from working as intended. Also check that the table passed to SUMX is the one whose rows should be multiplied; iterating a lookup table instead of the sales fact table can produce a different result.

Using SUM for an expression: SUM totals a single column. Use SUMX when each row needs a calculation before aggregation.
Unexpectedly large totals: Check whether the table contains duplicate order lines or whether a join or data import has duplicated rows.
Filters do not change the result: Verify relationships and confirm that the filter reaches the iterated table.
Blanks or odd results: Review blank values, data types, and the business meaning of missing quantity, price, or weight. Decide whether missing data should be excluded, treated as zero, or corrected at the source.

For most ordinary models, iterating the relevant fact table with a straightforward arithmetic expression is a reasonable approach. Performance can become a concern when the iterator scans a very large table or performs complex work for every row. Keep the expression as simple as possible, use a suitable star-schema model, and avoid unnecessarily expanding the table being iterated. Do not replace a correct calculation with a more complicated pattern just to avoid the word “iterator”; measure performance with the actual model and report workload.

DAX SUMX Compared with Excel SUMPRODUCT

Excel’s SUMPRODUCT works with worksheet ranges and arrays. DAX’s SUMX works over a model table and evaluates in filter context. The arithmetic may look similar, but table relationships, report filters, and measure evaluation make DAX behavior more context-dependent.

In a worksheet, a formula might refer directly to two ranges. In DAX, columns belong to named tables, and the expression is normally written as a measure or calculated column. There is no need to search for a DAX function called SUMPRODUCT: use SUMX for row-wise multiplication and summation, then add filters or a denominator if the business calculation calls for them.

Frequently Asked Questions

Does DAX have a SUMPRODUCT function?
No. Use SUMX with a multiplication expression to multiply corresponding row values and sum the results.

Why can’t I use SUM with two columns multiplied together?
SUM expects one column reference. SUMX iterates a table and can evaluate an expression such as quantity times unit price for every row.

Does SUMX respect slicers in Power BI?
Yes. A measure using SUMX is evaluated in the current filter context. Slicers affect its result when the model’s relationships allow their filters to reach the table being calculated.

How do I make a weighted average in DAX?
Use SUMX to sum each value multiplied by its weight, then divide by the sum of the weights. DIVIDE is a practical choice for handling a zero denominator.

Should I use SUMX or create a calculated column?
Use a measure with SUMX when you need a result that changes with report filters. Use a calculated column when you specifically need a stored row-level result for other model operations.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.