Forum Discussion
Cumulative Total
ElliotP Sorry about the original post. It was from my phone and had typos :smileywink:
Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)
Running Total COLUMN = CALCULATE ( SUM ( 'All Web Site Data (2)'[UniquePageviews] ), ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] ) )And as you can see it works! :smileyhappy:
And here's the MEASURE formula
Running Total MEASURE = CALCULATE ( SUM ( 'All Web Site Data (2)'[UniquePageviews] ), FILTER ( ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] ) ) )Which also works...
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.
Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist.
Thanks,