cancel
Showing results for
Did you mean:
Highlighted
Post Prodigy

## Cumulative Total

Hi,

I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, trying it as both a measure and a calc'd column; It always seems to refer me to the same number. So for example; March will be 10, April 12, but instead of showing me 10 for March and 22 for April, it shows me 10 for March and 12 for april.

For eg: https://gyazo.com/41bd333cedac290e6980772906ff0034 with a measure

I my Month column as a date column, I've tried using all kinds of features including, calc, sum, sumx, time based functions.

The commonly reccomended filter of [Date] <= MAX [Date] always returns an error. The Earlier function returns errors with concerns there isn't a function above it. Any help would be greatly appreciated; I've spent hours today trying to work this out and I get the feeling there is a difference in Pivottable dax and powerbi dax. I want to be able to shape and transform my data in powerbi, using dax in powerbi.

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion

## Re: Cumulative Total

@ElliotP Sorry about the original post. It was from my phone and had typos

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!

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...

49 REPLIES 49
Highlighted
Solution Sage

/sdjensen
Highlighted

## Re: Cumulative Total

@ElliotP

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,

Ryan Durkin

Highlighted
Post Prodigy

## Re: Cumulative Total

@sdjensen I have read that article and that's the base I've been working off, yet to no avail. When I either attempt to impose the measure on a table or create a new column with that data it simply gives me the same value that corresponds with the data.

Photos to better demonstrate the issue:

https://gyazo.com/ca41ce0b2d8ec572608d4afda4cffd32

https://gyazo.com/4e8d9b3e1cc38c514048272ced01a534

https://gyazo.com/14a6089654df6e90e7fd5595fd842ebd

The Date colum is set to a date, the Unique Pageviews is set to Whole Numbers. I'm honestly lost at this point.

Highlighted
Community Champion

## Re: Cumulative Total

@ElliotP

Replace this:

All(Allwebsitedata(2) [Date]),

By

All(Allwebsitedata(2)),

The reason is because you are using the date field in your data Table, if you'll use a calendar table the formula works perfects.

Lima - Peru
Highlighted
Post Prodigy

## Re: Cumulative Total

@Vvelarde

Thank you so much, we're making progress.

I've tried it as a measure as well as a new calculated column, yet it shows the cumulative total in each row; photos to demonstrate;

https://gyazo.com/0d365fcaaba2507bca2dffe1177837eb

https://gyazo.com/37b810f7b9f4659492b405b3362106db

As well, what do you mean in regards to date field date table. Should I set it to another table type?

Highlighted
Community Champion

## Re: Cumulative Total

@ElliotP

For a calculated column :

CumulativeQuantity2 =
VAR CURRENTDATE='All Web Site Data (2) '[Date]
RETURN
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2)');'All Web Site Data (2) '[Date]<= CURRENTDATE))

For a measure:

CumulativeQuantity-M =
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2) ');'All Web Site Data (2) '[Date]<= MAX('All Web Site Data (2) '[Date])))

Lima - Peru
Highlighted
Community Champion

## Re: Cumulative Total

@ElliotP Sorry about the original post. It was from my phone and had typos

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!

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...

Highlighted
Post Prodigy

## Re: Cumulative Total

Thank you so much guys, I really appreciate it. It has been doing my head in.

Why do we use the filter feature for the measure but not the column? I checked and the measure formula works for a new column as well, but I'm curious as to explanation of the difference.

As well, I know this is going to be all the more complicated; But I'd also like to calculate a moving and trailing average. I'll have a try myself again, but if either @Sean or @Vvelarde knows the forumla off the top of their head, that would be greatly appreciated.

Thank you so much.

Highlighted
Community Champion

## Re: Cumulative Total

@ElliotP Okay since you didn't mention how many Days or Month Average

Try this...

```Moving Average =
DIVIDE (
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] )
)
),
CALCULATE (
DISTINCTCOUNT ( 'All Web Site Data (2)'[Date] ),
FILTER (
ALL ( 'All Web Site Data (2)' ),
'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
)
),
0
)```

The Numerator is basically your Running Total Measure (so you actually can use the Measure name there) while

The Denominator is the number of days.

See picture to see how formula works

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors