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

     

Sean's avatar
Sean
Community Champion
10 years ago

ElliotP Okay since you didn't mention how many Days or Month Average

 

Try this...

Moving Average =
DIVIDE (
    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] )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'All Web Site Data (2)'[Date] ),
        FILTER (
            ALL ( 'All Web Site Data (2)' ),
            'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
        )
    ),
    0
)

 

The Numerator is basically your Running Total Measure (so you actually can use the Measure name there) while

The Denominator is the number of days.

 

See picture to see how formula works :smileyhappy:

 

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago

Thanks for the quick and explained reponse. I recieved the same thing; excep the Moving average values is the value for example for day 5 of 100, simply divided by 5 = 20. As opposed to being a running total divided by the number of days.

 

Something like

Day 1: 10

Day 2: 20

Day 3: 30

 

Day1avg: 10

Day2avg: 15

Day3avg: 20

 

I'll try and work it out, I'm trying to use the DATESBETWEEN function and some of the previousmonth and dateadd functions but I'm currently being told there are too few arguements (another issue).