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:

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi,

 

My requirement is to display the cumulative sum for Actual and Scheduled Quantity from the start of the current month to the current day of the current month. Scheduled Quantity would be for the entire current month.

 

Current Month is Feb so X-axis would have days from 1 to 28. Y-Axis would show both cumulative sum of actual and scheduled quantity.

 

The actual and scheduled quantity is from the same table which also contains date key. The date key is common between this table and the date table.

 

I tried the measure - 

Cumulative Scheduled Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Scheduled_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]<=MAX(D_DATE[Date_Key]))) but I only get one bar at the end. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Please help me with the same as I need it urgently. Thanks in advance.