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

     

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago

Ok, I'm attempting to calculate the Running Total Column; we've pulled and shaped the data again and redone some things; When i use the code:

Running Total Column = CALCULATE (DISTINCTCOUNT('All Web Site Data'[Date - Copy],DATESINPERIOD('All Web Site Data'[Date - Copy], LASTDATE('All Web Site Data'[Date - Copy]),-1,DAY )))

I recieve the error "too many arguements were pased to DISTINTCOUNT function. The maximum argument count for the function is 1."

 

Then when I add a bracket and have this as my code;

Running Total Column = CALCULATE (DISTINCTCOUNT('All Web Site Data'[Date - Copy]), DATESINPERIOD('All Web Site Data'[Date - Copy], LASTDATE('All Web Site Data'[Date - Copy]),-1,DAY ))

I recieve "A circular dependency was detected: All Web Site Data[Running Total Column], All Web Site Data[Month on Month Return], All Web Site Data[Running Total Column]."

 

It seems we need to isolate the running total column from the other two columns mentioned?

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    10 years ago

    I just tried:

     

    Month on Month Total Sessions = Calculate(DISTINCTCOUNT('All Web Site Data'[Cumulative Quantity1]), DATESINPERIOD('All Web Site Data'[Date - Copy], LASTDATE('All Web Site Data'[Date - Copy]),-1, MONTH))

    And recieved a circular dependency bug.

     

    Let me try using Sum instead of Distinctcount.

     

    Returned Circular dependency. hmmmm

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    10 years ago

    I deleted the interferring column and created it as both a column and as a measure.

     

    As a column it simply shows the same values as "Cumulative Quantity1" my already running total.

     

    I tried adding the columns and the measures to graphs on my report page; yet when i add cumulative total it seems to mess up and shows tremendously high numbers in total on a bar graph, as in, it's high from the very beginning, as opposed to building and becoming bigger and bigger each month for example.

     

    The same happens with my Moving average column.

     

    I get the sense the graph issue may have something to do with the way the graph is interacting with the columns.

     

     

     

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    10 years ago

    I've created a measure;

    Month on Month Total SessionsMeasuree = CALCULATE([Cumulative Quantity1M],
        DATESBETWEEN('All Web Site Data'[Date - Copy],                    /*DATESBETWEEN function returns a table of days based on begin & end dates.*/
            FIRSTDATE(PREVIOUSMONTH('All Web Site Data'[Date - Copy])),   /*PREVIOUSMONTH gets all the days from the previous month. FIRSTDATE returns the first day of that month.*/
            LASTDATE(DATEADD('All Web Site Data'[Date - Copy],-1,MONTH))  /*DATEADD allows us to navigate a number of periods back in time. LASTDATE gets the last date.*/
         )
     )

    But this occurs:

    https://gyazo.com/3ecd0c362afd630dfa71661d0a7dd1cd