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

     

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.