topic Re: Cumulative Total of Cumulative Totals in Desktop

Cumulative Total of Cumulative Totals

florestal — Thu, 01 Mar 2018 02:16:15 GMT

Hello,

Yet another question regarding cumulative totals...

I am monitoring my project's cash flow, and I want to assign a metric that would help me assess the cash position. I'm thinking of computing the area under the cash flow curve. In other words I take each day's balance and sum it to current running total. So basically it's a cumulative of cumulative totals.

I have my RunningBalance, which is defined as the following:

RunningBalance = CALCULATE( [Balance], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])) )

So I thought I could simply have another measure called RunningArea where I would just replace the [Balance] by the above [RunningBalance]. Here is the measure:

RunningArea = CALCULATE( [RunningBalance], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])) )

Unfortunately, I'm not getting the expected result. Both [RunningBalance] and [RunningArea] seem to be giving the same thing. Here is a screen grab of what I'm getting:

For the above situation, here is an Excel mockup of the desired output:

Any idea how I can get the cumulative of cumulatives?

Thanks for your help

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Thu, 01 Mar 2018 02:52:36 GMT

That sounds like a groups and supergroups problem:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Basically, an aggegation of an aggregation.

Re: Cumulative Total of Cumulative Totals

Ashish_Mathur — Thu, 01 Mar 2018 04:02:26 GMT

Hi,

Try these calculated column formulas

Balance = [Revenue]-[Cost]

RunningBalance = CALCULATE(SUM(Data[Balance]),FILTER(Data,Data[Date]<=EARLIER(Data[Date])))

RunningArea = CALCULATE(SUM(Data[RunningBalance]),FILTER(Data,Data[Date]<=EARLIER(Data[Date])))

Hope this helps.

Re: Cumulative Total of Cumulative Totals

florestal — Thu, 01 Mar 2018 13:19:55 GMT

Ashish_Mathur Thank you for your quick reply! Your solution gives me the expected results. The only issue is this works for calculated columns, which I had to insert in my Date table.

Since my Power BI dashboard allows for dynamic filtering, I would need for [RunningBalance] and [RunningArea] to be measures. Do you know if this can be achieved with measures as well?

Thanks

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Thu, 01 Mar 2018 13:21:30 GMT

florestal- Yes it can, see the article.

Re: Cumulative Total of Cumulative Totals

florestal — Thu, 01 Mar 2018 13:25:46 GMT

Greg_Deckler Thanks for your reply. I did read your blog post, but since I'm fairly new to Power BI, I'm not quite sure I understand all the subtleties. The key seems to be the SUMMARIZE function. I'll take my time to read it thoroughly, and let you know if it works for me. Thanks again!

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Thu, 01 Mar 2018 13:35:51 GMT

Yes, the key is the SUMMARIZE function, which generates the table of the aggregated values. You can then use that table to aggregate those values. A lot of times, I create a temporary "New Table" and get my SUMMARIZE function the way I want (sometimes it's way more than just a SUMMARIZE), create a measure based on that table and then I can easily translate the entire thing into just a single measure.

Re: Cumulative Total of Cumulative Totals

florestal — Thu, 01 Mar 2018 22:42:51 GMT

Greg_Deckler You've pointed me in the right direction. Thanks for that. I'm now very close, but still no cigar :(

Unfortunately, the SUMMARIZE function was not giving me the expected values in the output table. I spent hours trying to figure out why, and finally came across this blog post where the author says that SUMMARIZE has some bugs, and that SUMMARIZECOLUMNS should be used instead. Once I did, I had the right values for my intermediate table. Here's the statement:

TempTable = 
SUMMARIZECOLUMNS (
    'Date'[Date],
    "RunningBalance",
    CALCULATE ( 
        [Balance], 
        FILTER ( 
            ALLSELECTED ( 'Date' ), 
            'Date'[Date] <= MAX ( 'Date'[Date] ) 
        )
    )
)

And here is the output:

Now since I want the cumulative sum of that table, I figure I have to nest it inside another CALCULATE statement. So here's what I wrote:

RunningArea = 
CALCULATE (
    SUM ( [RunningBalance] ),
    FILTER (
        SUMMARIZECOLUMNS (
            'Date'[Date],
            "RunningBalance",
            CALCULATE ( 
                [Balance], 
                FILTER ( 
                    ALLSELECTED ( 'Date' ), 
                    'Date'[Date] <= MAX ( 'Date'[Date] ) 
                )
            )
        ),
        [Date] <= MAX ( [Date] )
    ) 
)

The problem is that PowerBI does not recognize the [RunningBalance] column, which is contained in the table returned by the SUMMARIZECOLUMNS function. Here's the error message I get:

So my question is this: how can I access the [RunningBalance] column from the dynamic table created? Am I going at this wrong? Your insight is very much appreciated.

Thanks in advance

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Fri, 02 Mar 2018 03:12:39 GMT

Very nice progress!! Try using SUMX instead of SUM. Feed your table created by SUMMARIZECOLUMNS into SUMX and SUMX will recognize your columns.

Re: Cumulative Total of Cumulative Totals

florestal — Fri, 02 Mar 2018 10:53:26 GMT

Greg_Deckler Okay I tried SUMX. Since both SUMX and FILTER need a table as their first argument, which in my case is the same, I figured it would be a good time to use a variable in order to prevent the statement from becoming unwieldy. So this is what I tried:

VAR
TempTable =
SUMMARIZECOLUMNS (
    'Date'[Date],
    "RunningBalance",
    CALCULATE ( 
        [Balance], 
        FILTER ( 
            ALLSELECTED ( 'Date' ), 
            'Date'[Date] <= MAX ( 'Date'[Date] ) 
        )
    )
)
RETURN

CALCULATE (
    SUMX ( 
        TempTable,
        [RunningBalance] 
    ),
    FILTER (
        TempTable,
        TempTable[Date] <= MAX ( TempTable[Date] )
    ) 
)

Power BI is unhappy, as apparently tables cannot be used as variables:

So I bit the bullet and copied the entire SUMMARIZECOLUMNS statement twice, well aware that this would be both inefficient and unreadable, but again no success. Now Power BI complains that I need an additional aggregate function in my FILTER expression. First, here is what I wrote:

RunningArea = 
CALCULATE (
    SUMX ( 
        SUMMARIZECOLUMNS (
            'Date'[Date],
            "RunningBalance",
            CALCULATE ( 
                [Balance], 
                FILTER ( 
                    ALLSELECTED ( 'Date' ), 
                    'Date'[Date] <= MAX ( 'Date'[Date] ) 
                )
            )
        ),
        [RunningBalance] 
    ),
    FILTER (
        SUMMARIZECOLUMNS (
            'Date'[Date],
            "RunningBalance",
            CALCULATE ( 
                [Balance], 
                FILTER ( 
                    ALLSELECTED ( 'Date' ), 
                    'Date'[Date] <= MAX ( 'Date'[Date] ) 
                )
            )
        ),
        TempTable[Date] <= MAX ( TempTable[Date] )
    ) 
)

And here is the error message:

So I just inserted the problematic TempTable[Date] inside a MIN aggregator. I know this will not give the expected cumulative sum, but at this point I just want Power BI to stop complaning :). Sure enough no syntax error is detected. However, when I try adding the newly-created [RunningArea] measure as part of my table, Power BI throws the following error:

This whole odyssey has been a great learning experience (failure is a great teacher), but I'm really at a loss as to what to do to make this work. Any final suggestions? I promise I will not bother you again after that :)

Thanks in advance

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Fri, 02 Mar 2018 12:26:24 GMT

No, feed your FILTER into your SUMX as your table. So, FILTER will return a table, use that as the input for SUMX for its table. So, "wrap" your FILTER statement with a SUMX statement. So something like:

RunningArea = 
    SUMX(
      FILTER (
        SUMMARIZECOLUMNS (
            'Date'[Date],
            "RunningBalance",
            CALCULATE ( 
                [Balance], 
                FILTER ( 
                    ALLSELECTED ( 'Date' ), 
                    'Date'[Date] <= MAX ( 'Date'[Date] ) 
                )
            )
        ),
        [Date] <= MAX ( [Date] )
    ),[RunningBalance]
)

Re: Cumulative Total of Cumulative Totals

florestal — Fri, 02 Mar 2018 12:45:40 GMT

Alright, so I put what you suggested:

RunningArea = 
SUMX(
    FILTER (
        SUMMARIZECOLUMNS (
            'Date'[Date],
            "RunningBalance",
            CALCULATE ( 
                [Balance], 
                FILTER ( 
                    ALLSELECTED ( 'Date' ), 
                    'Date'[Date] <= MAX ( 'Date'[Date] ) 
                )
            )
        ),
        [Date] <= MAX ( [Date] )
    ),    
    [RunningBalance]
)

I do not get any syntax error. However, when trying to add the [RunningArea] measure in my table, I get a similar error message to what I saw before:

Again thank you so much for all your help. I'll just do without this metric for now.

Have a great weekend!

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Fri, 02 Mar 2018 13:17:52 GMT

florestal- You gave it your best go, I took a few minutes this morning and sat down and hammered this out. Here you go.

Given an Enter Data query called "Running"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3NNM3MjCwUNJRMgBiQ1NTA0NjpVgdsJQ5TAqEoGIWWMQsMcWMDLCIGWIRM8IiZoxFzARFLBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Cost = _t, Revenue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Cost", Int64.Type}, {"Revenue", Int64.Type}})
in
    #"Changed Type"

With this Running Balance measure:

RunningBalance = CALCULATE( SUM([Revenue]), FILTER(ALL(Running), Running[Date] <= MAX(Running[Date])))

Created this Running Area measure:

RunningArea = 
VAR MyTable = SUMMARIZE(FILTER(ALL(Running),Running[Date]<=MAX(Running[Date])),Running[Date],"RunningBalance",[RunningBalance])
RETURN SUMX(MyTable,[RunningBalance])

Put it in a table visualization:

Re: Cumulative Total of Cumulative Totals

florestal — Fri, 02 Mar 2018 15:32:13 GMT

Greg_Deckler Wow, the stars are aligning for me not to have this work... Now I'm getting a "Not enough memory" error when trying to add the [RunningArea] measure to a table visual:

FYI my PBIX file is small (521 kb), but I do not have access to a lot of memory (4 GB). It's probably time for me to get a new machine :) I'm still marking this as the solution for others to know how this is done.

Again, thank you so much for taking the time to walk me through this.

Re: Cumulative Total of Cumulative Totals

Greg_Deckler — Fri, 02 Mar 2018 18:49:08 GMT

Awesome! Sorry, nothing I can do about the memory!! :)