A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals. For example, you may want to see total sales of a product as it accumulates over time, or for inventory models the total on hand at a given time. You can find more tips and tricks at my blog, www.bipatterns.com.
Let’s start with a base measure in a very simple pivot table.
Total Sales :=
CALCULATE ( SUM ( FactSales[SalesAmount] ) )
Now lets take our first attempt at computing a running total. This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away.
Cumulative Total Sales :=
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
)
)
Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed. The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ). This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.
If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.
The formula returns a number for dates that have no sales. We need to add some error handling, which is outlined below.
Cumulative Sales (Correct) :=
IF (
COUNTROWS ( FactSales ) > 0,
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
)
),
BLANK ()
)
The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank. You can see the difference between the two measures below:
If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.
