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 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
4 years ago

I've followed an identical approach as you have provided here but run into this issue when it comes to the current year context and the previous year calculating the full total as opposed to just the four months of this year.

 

  • neilcotton's avatar
    neilcotton
    Frequent Visitor
    4 years ago

    Hi Anonymous 

    Make sure the code you are using has ALLSELECTED rather than just ALL in the filter part of the expression. This should then reflect the various slicers that you have employed on the page.

     

    Regards

     Neil

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    Yep, I have that part in already

    App Cumulative =
    CALCULATE(
    [# Applications],
    FILTER(
    ALLSELECTED( 'Applications' ),
    'Applications'[APM_CREATED_DATE] <= MAX(Applications[APM_CREATED_DATE])
    )
    )
  • neilcotton's avatar
    neilcotton
    Frequent Visitor
    4 years ago
    Hi Anonymous, 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] )
    )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.