Writing a Subtotal Calc Column, AKA The Simplest Use of the Earlier() Function

 
I am a bit behind the 8-ball today, so here’s a quick topic.  It’s actually an excerpt from an older post, but one that has long deserved to be its own standalone post.

If you want to calculate a column in a table that is the total of all “similar” values in that table – meaning the total of all rows that have the same value as the current row for a particular column (or columns), this is what you need.

Say I have the following VERY simple table like this:

And I want to add a third column that is the total for each customer:

The calc column formula for that third column is this:

=CALCULATE(SUM([Amt]),
           FILTER(‘Table’,
                  ‘Table’[Customer]=EARLIER(‘Table’[Customer])
                 )
          )

When I am writing a calculated column and use the FILTER function, within the FILTER function, all of my references to columns in ‘Table’ will have “forgotten” all notion of “current row” and will instead be references to the entire column.

I repeat:  within the FILTER function, references to columns from the table being FILTERed (the table specified in the first parameter to FILTER), will not know what the current row is.  Column references outside of the FILTER function will remember the current row, just like they always do.

So the EARLIER() function is my escape hatch, to be used within the FILTER function, that allows me to go back and inspect the current row’s value.

This line of the formula:

  ‘Table’[Customer]=EARLIER(‘Table’[Customer])

Can be understood as:

  ‘Table’[Customer]=CurrentRow(‘Table’[Customer])

EARLIER is Probably Best Understood as CURRENTROW

In fact that’s a better name for EARLIER 99% of the time.  Just think of it as a CURRENTROW function, useful only in calculated columns, and only when you are performing FILTERS on ALL(Table), when you need to “jump back out” of the ALL and fetch a value from the current row.

Yes, it IS useful in other cases.  But I suspect that those other cases are rare enough that rather than a general purpose function like EARLIER, we should have been given a dedicated, easy to understand version named CURRENTROW.  Or maybe we should have both.  Yeah, both.  That would be good.