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

     

plaa's avatar
plaa
Frequent Visitor
8 years ago

Hi,

 

I got this expression working in a bar chart so that it shows running total correctly when [Date] field is selected as x-axis. However, if I use date hierarchy as x-axis, it won't work anymore. Instead of running total, it gives period's total. E.g. if Total Sales in January is 100 and 200 in February, it displays 100 for January and 200 for February, instead of 100 for Jan and 300 for Feb as it should. Does anyone know what might be the issue?

 

CALCULATE (
    SUM ( [TotalSales] );
    FILTER(
	ALL ( [Sales] );
        Sales[Date] <= MAX( Sales[Date] )
    )
)