Forum Discussion
Financial Statement with SummarizeColumns
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.
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.