SUM, SUMX, COUNTROWS, DISTINCTCOUNT & RELATED

Most business measures start with adding or counting something: total sales, number of bills, number of different customers. DAX has two families of functions for this. Aggregators such as SUM work on one column. Iterators such as SUMX go through a table row by row, calculate an expression for each row, and then add up the results. Knowing when you need an iterator is a big step in learning DAX.

In this lesson you will build Kesar Mart's sales, cost and profit measures, count rows and distinct values, and use RELATED to fetch a product's cost price while iterating over sales.

Aggregators: one column at a time

An aggregator takes a single column and returns one number for the current filter context:

For all 16 Kesar Mart lines, AVERAGE ( Sales[Amount] ) is 2,454.50, MAX is 3,900 (order 1010) and MIN is 1,299 (order 1007).

Prefer COUNTROWS ( Sales ) for "how many lines" over counting a column: it says exactly what you mean and does not depend on whether a column has blanks.

The error that teaches you SUMX

Suppose Sales had no Amount column, only Qty and UnitPrice. A beginner writes this measure:

Revenue = Sales[Qty] * Sales[UnitPrice]

Power BI rejects it:

A single value for column 'Qty' in table 'Sales' cannot be determined. This can happen
when a measure formula refers to a column that contains many values without specifying
an aggregation such as min, max, count, or sum to get a single result.

A measure has no "current row"; it sees a whole set of rows. And multiplying two totals, SUM ( Sales[Qty] ) * SUM ( Sales[UnitPrice] ), would be wrong too (76 × 11,311 is not revenue). You need to multiply per row, then add. That is what an iterator does:

Revenue = SUMX ( Sales, Sales[Qty] * Sales[UnitPrice] )

SUMX takes a table (Sales, already filtered by the visual) and an expression. It creates a row context for each row, calculates Qty × UnitPrice, and sums the results: 39,272, the same as SUM ( Sales[Amount] ). Other iterators work the same way: AVERAGEX, MINX, MAXX, COUNTX.

RELATED: looking up the one side

Profit needs the cost price, which lives in Products, not in Sales. Inside an iterator over Sales, RELATED follows the many-to-one relationship and returns the value from the matching product row:

Total Cost = SUMX ( Sales, Sales[Qty] * RELATED ( Products[CostPrice] ) )

Total Sales = SUM ( Sales[Amount] )

Total Profit = [Total Sales] - [Total Cost]

Margin % = DIVIDE ( [Total Profit], [Total Sales] )

Format Margin % as a percentage in Measure tools. By category:

Home products earn almost twice the margin of groceries. Notice the total margin is 23.30%, not the average of 16.51% and 29.61%: the measure recalculates 9,152 / 39,272.

RELATED needs two things: a row context (inside an iterator or a calculated column) and a relationship from the current table to the target table on the one side. If either is missing you see an error saying the column "either doesn't exist or doesn't have a relationship to any table available in the current context".

RELATEDTABLE: looking down to the many side

The opposite direction uses RELATEDTABLE, which returns the rows of the many side that belong to the current row. As a calculated column in Products:

Lines Sold = COUNTROWS ( RELATEDTABLE ( Sales ) )

Product             | Lines Sold
--------------------+-----------
Basmati Rice 5 kg   | 4
Masala Chai 500 g   | 3
Steel Water Bottle  | 3
Pressure Cooker 3 L | 4
Cotton Bedsheet     | 2

Because it is a calculated column, it is fixed at refresh. For a number that reacts to slicers, use a measure such as COUNTROWS ( Sales ) with Product in the visual instead.

DISTINCTCOUNT: how many different?

"How many stores sell this product?" is a distinct count:

Stores Selling = DISTINCTCOUNT ( Sales[StoreID] )

The pressure cooker has four lines but only three stores, because Pune sold it twice. COUNTROWS and DISTINCTCOUNT answer different questions; choose deliberately.

AVERAGEX over a dimension

"Average sales per store" is not the average of sales lines. Iterate over the stores instead:

Avg Sales per Store = AVERAGEX ( VALUES ( Stores[City] ), [Total Sales] )

VALUES ( Stores[City] ) returns the list of cities visible in the current filter context. For each city, AVERAGEX evaluates [Total Sales] (a measure inside an iterator is calculated for that one city) and then averages: (10,475 + 7,862 + 9,594 + 11,341) / 4 = 9,818. Filter the report to the North region and it becomes the average of Jaipur and Lucknow.

Common mistakes

• Multiplying two sums. SUM ( Qty ) * SUM ( UnitPrice ) is not revenue. Use SUMX for row-by-row maths.
• Using RELATED outside a row context. In a plain measure it fails. Wrap it in an iterator such as SUMX.
• Using RELATED in the wrong direction. From Products you cannot RELATED a sales column; use RELATEDTABLE.
• Counting a column when you mean rows. COUNT ( Sales[OrderID] ) skips blanks; COUNTROWS ( Sales ) counts every line.
• Averaging averages. The average of four city margins is not the company margin. Divide totals instead.

Key points

• Aggregators (SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT) work on one column; COUNTROWS counts table rows.
• Iterators (SUMX, AVERAGEX, MAXX ...) evaluate an expression for each row of a table, then aggregate.
• RELATED fetches a value from the one side of a relationship inside a row context; RELATEDTABLE returns the related rows on the many side.
• Build complex measures from simple ones: Total Cost, Total Profit, then Margin %.
• Ratios of totals, not averages of ratios, give correct totals.

Avg Line Value = AVERAGE ( Sales[Amount] )

Best Line = MAX ( Sales[Amount] )

Cost Share % = DIVIDE ( [Total Cost], [Total Sales] )