Rolling sum across categories that are not dates
December 13, 2011 7 Comments
Sometimes you may need to create a PowerPivot report that displays rolling totals on categories that do not reflect years, months or days. You might feel a bit stuck as most of the examples on rolling aggregates are related to time, for example Year to Date figures. Those can be implemented using DAX functions like TOTALYTD(), but as you examine the required parameters for that function you discover that a date column is necessary.
For example, take a look at the following table:
There are two categories used as row labels: [Color] and [SizeRange] (This data comes from the sample database AdventureWorks and represents sales by product attributes). If you need to display an aggreage figure showing the running total by [SizeRange] in descending order you of course won’t be able to use calendar based functions as TOTALYTD(). However, the effect can still be created using DAX formulas.
The first thing needed is to generate a numeric value that uniquely identifies the [SizeRange] data. We need this because we will use it in the Running Sum logic by telling the PowerPivot engine to aggregate a number of values up until the current value displayed. Those sort of operations are always easier to do when identifier columns are used.
If you are lucky to be using a database engine to populate the PowerPivot window, you can use their inherent functionality to generate a lookup table. For example, if the underlying database is SQL Server we could use the following statement to generate a new in-memory PowerPivot table:
row_number() over (order by sizerange desc) id,
SizeRange from (select distinct sizerange from DimProduct) a
Notice the order by statement in row_number(): I am specifically generating the identifier based on a descending order. This should match the way I order the row labels in the spreadsheet, in order to generate the ‘rolling sum’ effect.
Establishing a relationship to the Product table, the underlying data model looks like this now:
To make things easier, we now use the RELATED() function to invoke the identity column on the [SizeRangeLookup] table from the DimProduct table:
We now have all we need to generate the rolling sum effect. Adding the following DAX measure to the PowerPivot table will display the new aggregate in the spreadsheet:
IF( COUNTROWS( VALUES( DimProduct[SizeRange] ) ) = 1,
CALCULATE( SUM( FactResellerSales[SalesAmount] ) ,
SizeRangeLookup[id] <= VALUES(DimProduct[SizeRangeLookupId]) ) ,
ALLEXCEPT( DimProduct, DimProduct[Color] )
The measure uses CALCULATE() to affect filter context by selecting only the subgroup of rows that have a SizeRangeLookup[id] equal or lower than the currently displayed one. This in effects adds up all rows with [SizeRange] values that have been tagged with a numeric identifier up until the one that is currently in context. The resulting effect is displayed below:
As you can see, the ‘rolling sum by color’ measure incrementally adds up the categories in the row labels, but only until the last value in the current [Color]. It then restarts from the first value of the following [Color] and so on.
One of the interesting challenges of these type of rollups are the generation of identifier columns. It would probably be great to have some kind of DAX function that would to this for you on the PowerPivot window, with no need to generate it on the underlying data source or the query. On the other hand, having to use this method gives you more control as in some scenarios you may want to establish relationships via valid source identifiers and generating other ones in the PowerPivot window could make any potential relationships invalid.