**âAGAIN!â**

**-Pai Mei**

### SUMX() â the great iterator

Have you ever written an array formula in Excel? (Donât worry, most people havenât). Have you ever written a FOR loop in a programming language? (Again, donât worry, thereâs another question coming). Have you every repeated something over and over again, slowly building up to a final result?

Thatâs what SUMX() does. It loops through a list, performs a calc at each step, and then adds up the results of each step. Thatâs a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.

### Anatomy of the function

SUMX(<Table>, <Expression>)

**THE BRIDE:** âWhat praytell, is a five-point palm, exploding function technique?â

**BILL:** âQuite simply, the deadliest blow in all of the analytical martial arts.â

**THE BRIDE:** âDid he teach you that?â

**BILL:** âNo. He teaches no one the five-point palm, exploding function technique.â

Thatâs kinda how I feel about the description of SUMX in the Beta release: âReturns the sum of an expression evaluated for each row in a table.â It merely hints at the power within.

Oddly, the best way to show you what I mean is to start with some useless examples and then build up to useful ones. For all examples, I will use the following simple table, Table1:

### Useless Example #1: By the whole table

SUMX(Table1, Table1[Qty])

**Returns:** **35**, which is the total of the Qty column. Might as well just use SUM([Qty]).

**Why:** Well, it iterates over every row in Table1, and adds up [Qty] at each step, just like the description says it would.

### Useless Example #2: By a single column

SUMX(Table1[Product], Table1[Qty])

**Returns:** **An Error**

**Why:** Table1[Product] is not a Table, itâs a Column. And SUMX demands a Table as the first param.

### Useless Example #3: By distinct values of a column, sum another

OK, Iâll wrap the [Product] column in DISTINCT(), since that returns a single-column table:

SUMX(DISTINCT(Table1[Product]), Table1[Qty])

**Returns:** **An Error**

**Why:** [Qty] is not a column in the single-column table DISTINCT([Product]). Only [Product] is. Why did I even try this?

Thatâs where I gave up awhile back. Until I learnedâŠ

### Almost-Useful Example: The Second Param Can Be a Measure!

And even better, that measure CAN access other columns even if you use DISTINCT. First letâs define a [Sum of Qty] measure:

[Sum of Qty] = SUM(Table1[Qty])

And then re-try the previous example with the measure, not the column:

SUMX(DISTINCT(Table1[Product]), [Sum of Qty])

**Returns:** **35 **Yes, the total, again. But this time, the âWhyâ is worth paying attention to.

**Why:** Letâs step through it. Remember, for each value of the first param, SUMX evaluates the expression in the second param, and then adds that to its running total.

**Step One:** SUMX evaluates DISTINCT([Table1[Product]) which yields a single-column table of the unique values in [Product]:

**Step Two:** SUMX then filters the Table1 (not just the [Product] column!) to the first value in its single-column list, [Product] = Apples.

Then it evaluates the [Sum of Qty] measure against that table, which returns 17.

**Steps Three and Four:** The process repeats for Oranges and Pears, which return 13 and 5:

**Last Step:** SUMX then adds the three results it obtained: 17, 13, and 5, which yields 35.

A lot of work to get the same result that the [Sum of Qty] measure can get on its own, but now that you know how it operates, letâs do something else.

### And now, the Useful Example!

Letâs define another measure, which is the count of unique stores:

[Count of Stores] = COUNTROWS(DISTINCT(Table1[Store]))

For the overall Table1, that returns 2, because there are only 2 unique stores.

Letâs then use that measure as the second param:

SUMX(DISTINCT(Table1[Product]), [Count of Stores])

**Step One:** same as previous example, get the one-column result from DISTINCT:

**Step Two:** filter to Apples, as above:

âŠand the [Count of Stores] measure evaluates to 2 â 2 unique stores have sold Apples.

**Step Three:** Oranges

âŠagain, the measure evaluates to 2. 2 unique stores sold Oranges.

**Step Four:** Pears

âŠhey look, only one unique store sold Pears. So the measure evaluates to 1 here.

**Last Step:** Add them all up. 2 + 2 + 1 = 5. SUMX returns 5. This basically means that there are 5 unique combinations of stores and products that they sell.

### Why is that useful?

Well, I canât share the precise case I was working on, because it belongs to a readerâs business. But trust me, you are going to find yourself wanting this sooner or later.

### Things to keep in mind

**SUMX responds to pivot context just like anything else.**So if you slice down to just a particular year, your results will reflect only what Stores sold in that year.**AVERAGEX, MINX, MAXX, and COUNTAX all work the same way.**So if you want to iterate through just like SUMX but apply a different aggregation across all of the steps, you can. Those would return (5/3), 1, 2, and 3, respectively in our example.**The fields referenced in SUMX do NOT have to be present in your pivot view.**In my case, SUMX was working against [Store] and [Product]. But my pivot could just be broken out by [Region] on rows and sliced by [Year], and the measure still works. (I like to think of it as a stack of invisible cells underneath each pivot cell that you can see, and SUMX is rolling up a lot of logic across those invisible cells to return a simple number to the top cell you can see.)

### More to come!

Yeah, I am not even done with SUMX. Like Jules told you, itâs some serious gourmet DAX đ