SUMX and iterators in Power BI - Step-by-Step Guide

Introduction

SUMX helps you add up numbers by looking at each row one by one. It solves the problem when you want to do math on each row before adding, like multiplying price by quantity for every sale.

When you want to calculate total sales by multiplying price and quantity for each product.

When you need to sum values that depend on a calculation per row, like discounts applied per item.

When your data requires adding up results of a formula, not just a simple column sum.

When you want to create a measure that sums values from a filtered or calculated table.

When you want to iterate over a table to perform row-level calculations before summing.

Steps

Step 1: Open

- Power BI Desktop

Power BI Desktop opens with your report and data loaded

Step 2: Click

- Modeling tab

Modeling tab options appear

Step 3: Click

- New measure button

A formula bar appears at the top for entering DAX

Step 4: Type

- formula bar

You enter the SUMX formula, for example: SUMX(Sales, Sales[Price] * Sales[Quantity])

💡 Use table name first, then the expression to calculate per row

Step 5: Press

- Enter key

The new measure is created and appears in the Fields pane

Step 6: Add

- Report canvas

Drag the new measure to a card or table visual to see the total calculated by SUMX

Before vs After

Before

No measure exists to calculate total sales by multiplying price and quantity

After

A measure named Total Sales shows the sum of Price times Quantity for all sales rows

Settings Reference

New measure

📍 Modeling tab > New measure

Create a new calculation using DAX like SUMX

Default: No default formula

Common Mistakes

Using SUM instead of SUMX when multiplication per row is needed

SUM just adds a column and does not multiply price by quantity per row

Use SUMX with a table and an expression to multiply price and quantity for each row before summing

Writing the expression without specifying the table columns inside SUMX

SUMX needs a table and a row expression referencing columns from that table

Write SUMX(TableName, TableName[Column1] * TableName[Column2])

Summary

SUMX lets you add up results of calculations done row by row.

Use SUMX when you need to multiply or calculate per row before summing.

Remember to specify the table and the expression inside SUMX correctly.