cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElliotP
Post Prodigy
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
Sean
Community Champion
Community Champion

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

 

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! Smiley Happy

 

Running Total 2.png

 

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

 

Running Total 3.png

View solution in original post

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] )
)
)
sdjensen
Solution Sage
Solution Sage

Did you read this article? It really explains everything you need to know about cumulative total

/sdjensen

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

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

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

Vvelarde
Community Champion
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))

 

cumu.png

 

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])))

 

I hope this help you.




Lima - Peru
Sean
Community Champion
Community Champion

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

 

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! Smiley Happy

 

Running Total 2.png

 

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

 

Running Total 3.png

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

seancasey_1-1650453552578.png

 

Hi @seancasey 

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

Yep, I have that part in already

App Cumulative =
CALCULATE(
[# Applications],
FILTER(
ALLSELECTED( 'Applications' ),
'Applications'[APM_CREATED_DATE] <= MAX(Applications[APM_CREATED_DATE])
)
)

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] )
)
)

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.

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,

@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] )
))
AliceW
Impactful Individual
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]).

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

Regards

Neil

AliceW
Impactful Individual
Impactful Individual

Happy to help, Neil!

@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

thanks in advance !

WHEN I USE MEASURE

 
 
date districtresultsno of confirmed       
20-5-20APOS1      
21-5ANEG0      
22-5APOS1      
23-5ANEG0      
24-5BPOS1      
25-5BPOS1      
26-5BPOS1      
27-5CPOS1      

 

tamim
Frequent Visitor

Hi,

 

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

Could you please help me with the following:

So I want a column to show me : 

0.63

1.34

2.08

Capture.PNG

Thank you in advance

Crunchy
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,

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors