Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

81 REPLIES 81

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,

Hi @Anonymous 

 

Can you send us a pic of your data?

 

I assume Column1 is the date column, and Column2 has the number value?

 

Try to use ALLSELECTED instead of all.

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

 

You can also have a look at the TotalMTD, TotalQTD and TotalYTD DAX functions which work great for cumulative totals.

 

Dawid van Heerden

Twitter: @dawidvh

YouTube: https://www.youtube.com/davestechtips

**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

sofias
Frequent Visitor

Hello,

I've found your answer since I was struggling to build a cummulative column. However, I'm still struggling with the following:

Capture.JPG

1) RUNNING_NMV_FINAL  = MEASURE = 

IF(AND(ISFILTERED('dim pm'[payment]),ISFILTERED('DIM TIER'[TIER])),
CALCULATE (
SUM (Table2[NMV]),
FILTER(ALLEXCEPT(Table2,Table2[PM],Table2[TIER]),Table2[Index] <= MAX ( Table2[Index])
)
),
calculate(sum(Table2[NMV]),filter(all(Table2),Table2[Index] <= max(Table2[Index])))
)
If you notice, I have filters selected. And this measure only works when I have, either, one selection per filter or when no selection is done in ANY filter. 
 
2) Running nmv COLUMN  = column
CALCULATE (
SUM (Table2[NMV]),
ALL (Table2),
Table2[Index] <= EARLIER (Table2[Index])
) --> followed your logic
 
Although it is displaying the running total with no slicers selected, when I start to filter, it will not change dynamically as I need. 
 
In the end of this, what I would need to do is to divide each row of this cummulative_sales by the TOTAL_SALES_AMOUNT, which will be a %. Then I will do the same for the Losses (all the process before mentioned).
 
I need this because I want to create a line chart with 2 measures (cummulative sales for X axis and cummulative losses for Y axis) and on the legends I would put the country (so, 1  line per country). But the line chart does not allow me to put measures on the X axis, hence my need of using columns.
 
Can anyone help me please? I am starting to give up on this :(.
 
Thank you!!!
 
POB
Frequent Visitor

The pattern for cumulative totals in measures seems pretty well established using the formulation "<= MAX("

 

From testing it out myself, it appears that the MAX function in this case evaluates in the Row Context. 

 

CheckMax formula.JPG

 CheckMax table.JPG

 

 

 

 

 

What I don't understand is why is does this rather than evaluate in the Filter Context, which the description seems to imply it will do:

 

maxfunc.JPG

 

 

@Sean What if we have multiple rows for same date?

Anonymous
Not applicable

Hello; I am trying to create a table just like this. I followed the DAX but could not go through. Can you help me with this?

I already have the formila but cannot get it right.

 

Untitled.jpgUntitled2.jpg

It looks good to me, try using ; instead of ,

Hi,

 

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

 

P

Hi,

 

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

 

P

koenmilt
Frequent Visitor

Hi,

 

I tried the measure stated in the chosen solution (by Sean). This works!

 

My measure is as follows:

 

Cumulative Hours spend =
CALCULATE (
sum('OVERUREN_WEEK'[Hours Spend]);
FILTER (
ALL ('OVERUREN_WEEK'[Year_Week]);
'OVERUREN_WEEK'[Year_Week] <= MAX ( 'OVERUREN_WEEK'[Year_Week )
)

 

In my report I want to have a table visual that has three attibutes/columns, being: Year_week, Cost_center and Employee.

With the current DAX, the cumulative kinda works, it is cumulative by year_week, and breaks by all other attributes in the table visual (so Cost_center and Employee).

However, I only want it to break by Employee, it should keep cumulate when an employee switches cost_center.

 

Example:

 

Employee  - cost center - function - year_week - hours spend - cumulative

Henk         -  2500           - Developer - 201701   - 3                   - 3

Henk         -  2500           - Developer - 201702   - 1                   - 4

Henk        -  4000            - Developer - 201703   - 2                   - 2

 

The cumulative restarts when the employee switches to a different Cost_center in week 201703.

The only way I was able to resolve was by removing Cost_Center from the table, But I dont want to do that.

zq
Frequent Visitor

Hi there, I want to create a Running Difference instead of the Running Total (Sum) in POWER BI Table. Can you please guide how to achieve this?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.