Grouped running total with Power Query M

[webportal]

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!

[v-cazheng-msft]

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]

@webportal 

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.

[webportal]

I've tried to perform the calculation in a data flow within a Premium capacity and gave up after 12 hours of processing.

[v-cazheng-msft]

@webportal 

What data source are you using? Does it support Query folding? This might help you: Incremental refresh for datasets. Or firstly aggregate or calculate the data in the data source(such as Excel/SQL Server) before using Power BI Desktop.

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]

It is a CSV file. It won't fit an Excel - it has 1.7 million rows 😉

[v-cazheng-msft]

@webportal 

Well, I don't know what to do except reducing the amount of data.  😁

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]

@amitchandak  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?

[amitchandak]

@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.