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

 

koenmilt's avatar
koenmilt
Frequent Visitor
8 years ago

Hi,

 

I tried the measure stated in the chosen solution (by Sean). This works!

 

My measure is as follows:

 

Cumulative Hours spend =
CALCULATE (
sum('OVERUREN_WEEK'[Hours Spend]);
FILTER (
ALL ('OVERUREN_WEEK'[Year_Week]);
'OVERUREN_WEEK'[Year_Week] <= MAX ( 'OVERUREN_WEEK'[Year_Week )
) )

 

In my report I want to have a table visual that has three attibutes/columns, being: Year_week, Cost_center and Employee.

With the current DAX, the cumulative kinda works, it is cumulative by year_week, and breaks by all other attributes in the table visual (so Cost_center and Employee).

However, I only want it to break by Employee, it should keep cumulate when an employee switches cost_center.

 

Example:

 

Employee  - cost center - function - year_week - hours spend - cumulative

Henk         -  2500           - Developer - 201701   - 3                   - 3

Henk         -  2500           - Developer - 201702   - 1                   - 4

Henk        -  4000            - Developer - 201703   - 2                   - 2

 

The cumulative restarts when the employee switches to a different Cost_center in week 201703.

The only way I was able to resolve was by removing Cost_Center from the table, But I dont want to do that.