Day 10 — Iterators, ranking and top-N

Row-by-row calculations, and answering "which are the best five?"

The X functions, recapped

An iterator takes a table, evaluates an expression on every row, then aggregates. That is all.

Row by row is not the same as total by total

The first two columns match on every row, which is the check that the rebuild is correct. The third is a genuinely different question — margin per unit sold, which is far more comparable across categories than total margin.

Filtering inside an iterator

The two revenue figures add to the full 30,37,800 — always worth checking when you split a population in two.

Averages of averages

AVERAGEX(Sales, ...) averages over sales rows Not over products, not over regions, not over months. If a stakeholder asks for "the average monthly sales", averaging the 48 sales rows gives the wrong answer — you would need to iterate over months. The table you pass to an iterator defines what "average" means, and choosing it carelessly is a common and invisible mistake.

Ranking, the honest way

Power BI has RANKX, which needs a full table and careful handling of ties. This playground does not implement it, and a tutorial that pretends otherwise would be teaching you something you could not run.

What RANKX looks like in real Power BI Rank = RANKX(ALL(Region[Region]), [Total Sales])

ALL(Region[Region]) supplies the full list to rank against — without it, each row would rank only against itself and every rank would be 1. That ALL is the same escape-the-context idea from Day 7.

Top-N without RANKX

You can answer most top-N questions with what you already know. "How much comes from products above a threshold?":

Two products out of six generate 84.7% of revenue. That is the kind of finding a report exists to surface.

Comparing each row to the best

Read the nesting carefully The inner CALCULATE turns the row context created by MAXX back into a filter context, so the SUM respects the region being iterated. That conversion is called context transition, and it is the most advanced idea in this tutorial. If it does not click today, come back to it — everyone needs it explained twice.

North is the strongest at 11,26,400, so its gap is zero and every other region shows how far behind it is.

Try these yourself

Use SUMX to total Qty multiplied by 2.
Find the largest single-order margin.
Total the revenue from orders with a quantity below 5.
Explain why RANKX needs ALL() to work.
Work out what share of revenue comes from the Accessories category.

← PreviousDay 9 — Time intelligence — comparing to last year Next →Day 11 — Building the report — visuals that get read