Financial Statement with SummarizeColumns

[Sjohnson]

**I've edited this post with some more details resulting from experiments, and re-worded it to hopefully be a bit more clear**
Context
I've got a Power BI report that is meant to replicate a financial statement from a General Ledget. It handles the issue of aggregating subtotals like "Net Income" and "Cash from Operators" using a SWITCH statement in a measure, as proposed by Imke at BIAccountant and in this post. This has resulted in the creation of about 74 aggregators in a single switch. Performance up to this point is excellent.

However, the report goes on to apply various transformations to the initial measure (ie. % growth in profit/unit over a trailing period). Performance falls apart once I start applying these transformations, largely attributable to RAM bloat during the calculations (ie. when a user changes a slicer). Calculations take minutes, and can crash my computer (i7 with16GB RAM). I believe that part of this issue has to do with the report re-calculating the initial SWITCH at multiple points during the various normalization steps.

Using SummarizeColumns
I'm thinking that a way to improve performance would be to change the SWITCH from executing in a Measure, which presumably executes repeatedly as a 'front end'-type calculation, to being executed in a Column or a Table. I have managed to do this by using SummarizeColumns to re-create the entire General Ledger (ie. records dataset), along with the aggregator rows, which I have injected into the table using the same SWITCH. It works to re-create the same results as the measure, and performance is similarly very fast. However, I haven't had the time to build out and test the aggregators yet.

Question for the Community
Does my logic hold that the SummarizeColumns table will improve performance by calculating only once, vs a Measure which calculates repeatedly on each change in a filter/slicer?

Appreciate any help or thoughts!

[Sjohnson · Accepted solution]

I'm going to go ahead and answer my own question, as I appear to have found the answer through experimentation.

Pushing the SWITCH statement into a SummarizeColumns formula seems to have dramatically improved performance in my late-stage calculations.. No more crashing, no more ridiculous wait times I'm not 100% sure of the technical reasons behind this, but it is my understanding that a calculated table renders 1-time on execution, whereas a measure needs to re-calculate repeatedly as it is referenced in different measures later on. 

The structure of the solution is therefore as follows:
NewTable =
SummarizeColumns( GL[Category1], GL[Category2], GL[Date], ProductUnits[ProductName],
    "Values" = SWITCH( <This Workflow>) 
)
Where Category1 is a sub-total value (ie. Net Profit), and Category2 aggregates discrete GL#s into named categories (ie. Operating Expenses).

There's a little bit more to it than that, but the point is that the SWITCH is now generating everything at once upgront as an eager load, rather than as an eventual lazy-load (I think).

The formula winds up being quite long, as it requires one line in the switch for each aggregator (ie. "Net Income", "Gross Margin", etc...) but it renders very quickly. From there, additional transformations (such as "% Growth in Profit per Business Unit") can be calculated as column items, rather than measures, which dramatically improves end user experience.

If there are any bloggers that stumble into this post, I'd highly recommend a writing a post that compares the memory usage of a Switch-based financial statement in a Measure vs Calculated Table, both on their own and during execution in a referencing measure. As far as I can tell, this opens up some excellent opportunities for actual inancial analytics in Power Bi, rather than just reporting a vanilla financial statement. More than happy to discuss further in DMs.