Forum Discussion
Impactful IndividualGrouped running total with Power Query M
This is how my table looks like (1.7 million rows):
I'm trying to build a running total per customer ID and date.
This is easy to express using DAX, but unfortunately I don't have enough memory on my machine (16GB RAM).
So, I'm trying to find an alternative with Power Query M using buffered tables, etc. but that it too complicated for me.
Can anyone help? Thank you so much in advance!
11 Replies
- amitchandak
Super User
webportal , Try a measure like
Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date]) && Sales[Customer ID] =max(Sales[Customer ID])))
with a date table
Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(allselected(Date),Date[Date] <=max(Date[Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
webportalamitchandak thanks for the tip, but I want to calculate the accumulated sales per customer so I can classify each customer at a certain moment, so the calculation is static.
- amitchandak
webportal , You can try the DAX column, but I doubt it can be faster than M
Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter((Sales),Sales[Sales Date] <=earlier(Sales[Sales Date]) && Sales[Customer ID] =earlier(Sales[Customer ID])))
- webportal
amitchandak exactly.
But how to do this in M?
v-cazheng-msftCommunity Support
Hi webportal
Has your problem been solved? I've seen an issue quite similar to yours on another platform and it has been resolved. Here is the link Grouped running total with Power Query M.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- webportal
No, it hasn't been solved.
In fact, I believe it is IMPOSSIBLE to do a running total in Power Query with a 1.7 million row table.
- v-cazheng-msft
Yes, it largely depends on the performance of your computer. Even if the data in Power Query has not been loaded into the Desktop, the processing of the data by Power Query still consumes resources such as CPU. The amount of data is too large and the calculations are complex, which may cause Desktop to crash. You can consider removing unnecessary data in Power Query first, and use Filter Rows to reduce the amount of data before performing calculations. You can also consider pre-processing and doing calculation at the data source, then use Desktop to get the data from the data source.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
