cancel
Showing results for
Did you mean:
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
Community Champion

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

70 REPLIES 70
Anonymous
Not applicable
Hi
I try the formula above but I can't get results, I don't know why. Can you help?

CALCULATE (
SUM ( vwExecucaoMensalItem[BudgetPurchaseValue] ),
FILTER (
ALL (vwExecucaoMensalItem[Date] ),
vwExecucaoMensalItem[Date] < MAX ( vwExecucaoMensalItem[Date] )
)
)
Solution Sage

/sdjensen
Post Prodigy

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

Community Champion

@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
Post Prodigy

@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?

Community Champion

@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
Community Champion

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

Regular Visitor

I've followed an identical approach as you have provided here but run into this issue when it comes to the current year context and the previous year calculating the full total as opposed to just the four months of this year.

Regular Visitor

Make sure the code you are using has ALLSELECTED rather than just ALL in the filter part of the expression. This should then reflect the various slicers that you have employed on the page.

Regards

Neil

Regular Visitor
Yep, I have that part in already

App Cumulative =
CALCULATE(
[# Applications],
FILTER(
ALLSELECTED( 'Applications' ),
'Applications'[APM_CREATED_DATE] <= MAX(Applications[APM_CREATED_DATE])
)
)
Regular Visitor
Hi @seancasey, here is an example I am using that works for all filter selections on related tables. I am doing my date segmentation based on a calendar table.

CumulativeInvoiceQty =
CALCULATE
SUM ( FACT_SalesOrderLines[InvoicedSOQty] ),
FILTER
(
ALLSELECTED ( 'DATE_TABLE' ),
('DATE_TABLE'[Date]) <= MAX ( DATE_TABLE[Date] )
)
)
Regular Visitor

thanks @neilcotton , I think the lack of a date table is probaly what's causing me the trouble I'm having. Will add one now and see where it gets me.

New Member

Hi,

Thanks for this measure as it perfectly works. However it doesn't sync with any other filter options then. For example I have a slicer and want to filter on a specific area but the chart takes all the data instead of the slicer filter. I guess it's because we're doing an All filter option in the formula. How can I fix this so that my cumulative chart also syncs with my slicer?

Regards,

Helper III

@Sean  Hi I tried using the measure and I cant get it to work. I dont have sales volumes in column format. I can only use Sales Volume as measure for the caluclation. Is there a way to make moeasure to work when using measure?

This is how the measure looks. Thank you.

Cumulative Sales volume =
CALCULATE (
SUM ( 'Actuals'[Sales Volume] ),
FILTER (
ALLSELECTED ( 'Date' ),
'Date'[Date] <= MAX ( 'Date'[Date] )
))
Impactful Individual

Thank you so much for the formula!

I made an adjustment so that various filters applied in the page would work: instead of ALL(), i've used ALLSELECTED([Date]).

Regular Visitor

Alice, you are a life saver. I've been trying to figure this out for 2 days.

Regards

Neil

Impactful Individual

Happy to help, Neil!

Helper I

@Sean

Hi sir running total measure is workong in my case but it does not show differnt value for differnet categories .

I want to show a cumulated covid cases as per district but I cant do it I tried many formula sice two day

someone help me

WHEN I USE MEASURE

 date district results no of confirmed 20-5-20 A POS 1 21-5 A NEG 0 22-5 A POS 1 23-5 A NEG 0 24-5 B POS 1 25-5 B POS 1 26-5 B POS 1 27-5 C POS 1

Frequent Visitor

Hi,

I am intersted in this method, I tried to apply but it didnt work.

So I want a column to show me :

0.63

1.34

2.08

Frequent Visitor

You saved my sanity--thank you!

Anonymous
Not applicable

I am trying to calculate the running sum of a column but I am not getting it.

Please take a look at the formula and tell me where I am going wrong.

Cumulative = CALCULATE(SUM('dummy'[Column2]), FILTER(ALL(dummy),('dummy'[Column1])<=MAX('dummy'[Column1])))

Thanks,

Announcements