Forum Discussion
Power BI Running Total Memory fault
I have a table based on unique numerical values. I counted the number of occurrences for each in the next field. Finally I have a field that gives me a number for each row showing the average number of occurrences each month.
Now I want to have a running total on these average numbers, adding one row to the previous row, all the way through the 213,000 rows of the table. I have tried to use DAX formulas for this that I have found online, but when I try to execute it fails with a not enough memory error.
Funny thing: I built the same table in Access and was able to execute using a DSum formula.
Why is this so difficult? I want to perform this calculation in Power BI. I have much more to do with this report.
My Running total will be key to further analysis, and is a data field I will use in a calculation to select records. As such, my whole report depends on me getting this figured out.
What am I missing?
jpdejongjr great suggestion by DataInsights on Visual Calculation but keep in mind it will only work within those visuals. If you want to use it elsewhere you need to add measures, check this video on my channel and see if it helps How new WINDOW DAX functions and PARTITIONBY helped to get the running total in Power BI - Part 2 (youtube.com)
3 Replies
jpdejongjr great suggestion by DataInsights on Visual Calculation but keep in mind it will only work within those visuals. If you want to use it elsewhere you need to add measures, check this video on my channel and see if it helps How new WINDOW DAX functions and PARTITIONBY helped to get the running total in Power BI - Part 2 (youtube.com)
Have you tried visual calculations (link below)? This is a new feature that can be more performant than traditional ways of calculating running totals. Depending on your model, another option is to push the running total logic as far upstream as possible (SQL --> Power Query --> DAX calculated column); this approach won't recognize user filters, however.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
I discovered a solution by Enterprise DNA:
https://www.youtube.com/watch?v=f5k4fD1YJrQ
My next issue seems to be getting distinct Values using a measure instead of a calculated column...
Onward!
