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

     

BrentonC's avatar
BrentonC
Helper I
6 years ago

drnareshchauhan 

It's a bit hacky although you could create references of the table and filter into each site (If there were not too many) Use the solution supplied to accumulative sum based on the date. Then union all of the tables... although I am sure there must be a better way...
EDIT: 
I am ashamed I even thought of that, a much better way to sum on two columns in your situation is using Python

 

# 'dataset' holds the input data for this script
dataset['cumsum'] = dataset.groupby(['site','datepd'])['number'].cumsum()

 



Notes:

Pandas does not like returning dates properly, create a duplicate column which is date type datepd of your date column.
Change your original date to text so you have a reference of the date afterwards.
Use the code above.

Select the dataset.

Remove the datepd column

Change the date column to date type again.

SHould be good to go.



Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for solution. Could you please suggest how we can wite DAX / Measure formula to get cumulative sum like this..