Iterators (SUMX) & RELATED

Power BI · DAX (Data Analysis Expressions)

Theory

Iterator functions, the X functions such as SUMX, AVERAGEX, MINX, MAXX and COUNTX, go through a table row by row, evaluate an expression for each row (in row context), and then aggregate the results. SUM(Sales[Amount]) can only add an existing column; SUMX(Sales, Sales[Qty] * RELATED(Products[Price])) calculates a line value for every order and adds those up.

RELATED fetches a value from the 'one' side of a relationship for the current row, like a lookup that follows the model: on the Sales row for order 103 (ProductID P3), RELATED(Products[Price]) returns 500, so the line value is 3 × 500 = ₹1,500. RELATEDTABLE goes the other way and returns the related rows from the many side. Grouped by product, the measure gives Headphones ₹1,800, Keyboard ₹2,400 and Desk Lamp ₹2,000.

Why we use it

Many business values do not exist as a column: revenue from quantity times price, weighted averages, margins. Iterators compute them on the fly without adding stored columns.

When to use it

Use an iterator when the value must be computed per row before aggregating, or when you need per-entity results like AVERAGEX over Customers. If the per-row value is simple and needed often, a column in Power Query may be cheaper.

How it works

  1. SUMX(Sales, expression) starts
  2. Take the next Sales row (row context)
  3. RELATED fetches Products[Price] for that row
  4. Evaluate Qty × Price
  5. More rows? Yes: Go to the next row. No: Add up all row results.
  6. Return the total

Syntax

SUMX(<table>, <expression>)
AVERAGEX(<table>, <expression>)   MAXX / MINX / COUNTX(<table>, <expression>)
RELATED(<column>)            // from many side to one side
RELATEDTABLE(<tableName>)    // from one side to many side

Example

Revenue = 
SUMX(
    Sales,
    Sales[Qty] * RELATED(Products[Price])
)
// Row 101: 2 × 600 = 1,200 ... total 6,200

Avg Order Value = AVERAGEX(VALUES(Sales[OrderID]), [Total Sales])  // 6,200 / 6 = 1,033.33

// Calculated column on Customers
Order Count = COUNTROWS(RELATEDTABLE(Sales))  // Asha 2, Ravi 2, Meera 1, Karan 1

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic