PowerPivotPro

Revisiting ALL()

Yes, I’ve used ALL() a number of times already.  But I’ve never covered it directly – it’s always been a side topic to something else.  So I thought it was time I went back and made sure I had a specific post on it.

The short version is: ALL() removes filters from specific fields during a measure calculation.  It “De Niros” your measure… but in a controlled manner.

Syntax

    ALL( table_or_column, [column1], [column2], etc.)

So the first parameter is required, and can either be a single column or an entire table.

After that, you can specify as many other columns as you’d like…  as long as the first parameter didn’t specify a table. If your first  param is a table, you can’t specify additional columns.

Also note that even when specifying columns, the ALL() function requires you to always include the table name.

Lastly, all columns listed must be from the same table.

Legal Examples:

1. ALL(Table1)
2. ALL(Table1[Column1], Table1[Column2])

Illegal Examples:

1. ALL([Column1])
2. ALL(Table1, Table1[Column3])
3. ALL(Sales[Column1], Customer[Column2])

When to use ALL() – Example #1

You never use ALL() by itself.  You always use it in the context of another function.  It’s all about setting context for a calculation.  It is not a calculation unto itself.

The simplest place to use ALL is in a measure that always shows you a grand total of something, regardless of what filters are in play.  Revisiting the Temperature Mashup demo for a moment, here’s a pivot sliced by Temperature, showing a normal Sum of Order Quantity:

 
Now I add a measure that sets the Temperature table to ALL:

    [AllTempsQty] = CALCULATE(
                      SUM(Sales[OrderQuantity]), 
                        ALL(Temperature)
                    ) 

That ends up looking like:

(If you need a refresher on CALCULATE, which is probably the most useful function in all of DAX, check out this post.)

So, on Hot days, we sold a total of 2,245 Accessories.  And overall, across all temperatures, we sold 36,092 Accessories.

But note that the numbers are different across Category for both measures.  That’s because [AllTempsQty] did not use ALL against the Category columns (or table).

Cool.

Example #2 – % of Total

OK, but in many cases, you don’t actually want to see [AllTempsQty] in the pivot.  You just want to compare the filtered version of a measure to the unfiltered version.

So we can define something like:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity])  / 
                       CALCULATE(
                          SUM(Sales[OrderQuantity]),
                          ALL(Temperature)
                       )

Which gives us:

Note that I switched Excel’s number formatting for this measure to be Percentage, keeping me from having to include * 100 in my measure.

Of course, if I wanted to keep my [AllTempsQty] measure, then I could have defined my new percent total measure as the following instead:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity]) / 
                       [AllTempsQty]
                    

And that would have produced the same results.

Example #3 – Using ALL() in places other than CALCULATE()

If we revisit the post on derived measures, ALL() is an excellent modifier to use against an existing measure.

Let’s take a measure I defined in the Temperature Mashup, the [Qty per Day] measure:

And then we define a new measure:

  [QtyPerDayAllTemps] = [QtyPerDay](ALL(Temperature))
                 

That yields:

You like?  I like

“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:

 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 DISCTINT(), 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

1. 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.
2. 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.
3. 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

Mmmm! Daaaamn, Jimmie! This is some serious gourmet DAX! Usually, me and Vince would be happy with some freeze-dried Taster’s Choice right, but he springs this serious GOURMET DAX on us!

-Jules Winfield

 
 
 
What I’ve seen so far is great, and yet…

The things I have done with DAX so far are a HUGE boost to what I can do in Excel formulas alone.  Here are some of the functions I have explored to date:

1. RELATED – a faster, simpler VLOOKUP.
2. CALCULATE – the big Kahuna.  A version of SUMIFS that I can use in a pivot measure to remove, alter, or override pivot filters and just aggregate the source rows that I want.
3. ALL – the ability to strip pivot filters from certain fields or tables during a measure calc, enabling very flexible custom ratios when combined with CALCULATE.
4. DISTINCT – returns the set of unique values of a particular column within the current pivot filtering context, very useful (so far) when combined with COUNTROWS.
5. COUNTROWS – no more need for finding the unique-valued column in a current context, you can just count the source rows that meet current filter criteria.

But I’ve seen some things over the past week that made me realize:  I’ve been lax…  about DAX.

I just found a cupboard full of “gourmet DAX”

Funny thing folks:  in many ways, I am learning right alongside of you when it comes to DAX.  When I left Redmond in August, DAX was just starting to appear in the internal builds.  So while I was getting a little bit of an advanced look, in other ways I’ve been watching the DAX movie in progress just like everyone else.

Last week, a reader asked me a series of questions basically saying “can you do this in DAX?”  These were crazy things they wanted to do – VERY useful stuff for them, and totally understandable why they wanted it.  And my initial answer was “um, no, DAX can’t do that.”

I was wrong.  DAX very much CAN do those things.  And many, many other things I had not considered.  I’m a little embarrassed by holding out on you guys, but I’ve been holding out on myself, too.

I mean, I knew I had not yet dived into the time intelligence stuff.  Other people are doing a decent job of that, and my intent was to build up to it, slow and steady.  But I was missing other things, too.

New resolution:  at least two DAX posts a week

This is a long time coming really.  It’s time to devote a much bigger chunk of my efforts toward educating everyone – myself included – on the capabilities and finer points of DAX. 

Here’s a quick preview of what’s in store for us:

1. SUMX() – the FOR loop of DAX, kinda like Array Formulas in Excel
2. CALCULATETABLE() – generate a custom filtered table, on the fly, and then use that as the table argument to another function
3. Using one measure as an input to defining another – self explanatory in some ways, but you won’t believe how often this gets you out of a jam.
4. Using a different formula at one level of a pivot vs. another – just one example of something you can do by combining some of these techniques.

Seriously, I am excited.  I’m like a kid who’s discovered a secret room in the castle.