Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago
Solved

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 measu...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    10 years ago

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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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,
dawidvh's avatar
dawidvh
Helper I
7 years ago

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.