Forum Discussion
Helper IIIRunning Total Non Date - Not Enough Memory
Good Day,
I am trying to calculate the running total of open orders. The table of concern is SalesOrders.
It has nearly 1 million rows while open rows only make up less than 10k.
I have not been able to validate if any of my attempts have worked since I keep running out of memory, which means I am completely inefficient even if one of my tests do "work". I need a new messure that is efficient to calculate the running total of open orders.
Data Sample:
| BranchID | CustomerID | SalesOrderID | Line Number | Status | Total Price |
| A | 111 | S100 | 1 | Open | 100 |
| A | 111 | S100 | 2 | Open | 200 |
| B | 111 | S102 | 1 | Open | 300 |
| B | 222 | S103 | 1 | Closed | 400 |
| C | 222 | S104 | 1 | Open | 500 |
| C | 333 | S105 | 1 | Closed | 600 |
Expected Outcome:
| SalesOrderID | TotalPrice | Running Total |
| S104 | 500 | 500 |
| S102 | 350 | 850 |
| S100 | 300 | 1150 |
Notes/Messures:
It is possible for some SalesOrderID to appear more than once.
The visual will need to be dynamic to the different filters on BranchID, CustomerID so I have had been using ALLSELECTED.
Ive tried using a sumarize/summarizecolumn functions to display a table with only open status.
CALCULATE(SUM(SalesOrders[TotalPrice]), SalesOrders[Status] = "Open")
10 Replies
- Ashish_Mathur
Super User
Hi,
These are the measures i wrote
Price = SUM(Data[Total Price])Rank by price of Sales order = if(HASONEVALUE(Data[SalesOrderID]),RANKX(ALL(Data[SalesOrderID]),[Price]),BLANK())Cumulative price = SUMX(TOPN([Rank by price of Sales order],CALCULATETABLE(VALUES(Data[SalesOrderID]),ALL(Data[SalesOrderID])),[Price],DESC),[Price])Hope this helps.
EnrichedUserThank you for your time and quick reply. I spent a while this today working through this one. Sadly, I was not able to use as it still took too long to run.
I think the primary issue is that, I am only considering about 10k rows that are open and need to filter out the other 1m.Hi EnrichedUser ,
The total is incorrect because my sample data S102 is 300 when it is Open, not 350Change the sample data and enter the above formula. The result is:You can use our formula to compare the loading time
The loading time is too long. You can optimize the Dax function to reduce useless data and clear the Dax cache. For details, you can check these links:https://maqsoftware.com/expertise/powerbi/dax-best-practices
https://powerpivotpro.com/2019/03/dax-optimizations-write-it-like-the-dax-calls-it/
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi EnrichedUser ,
Here are the steps you can follow：
1. Create calculated table.
index = RANKX('Table',[SalesOrderID],,DESC,Dense)2. Create measure.
Total_Price_Measure = CALCULATE(SUM('Table'[Total Price]),FILTER(ALL('Table'),'Table'[SalesOrderID]=MAX('Table'[SalesOrderID])&&'Table'[Status]="Open"))Running Total = var _1=SUMX(FILTER(ALLSELECTED('Table'),'Table'[index]<=MAX('Table'[index])&&'Table'[Status]="Open"),[Total Price]) return IF(MAX('Table'[Status])="Closed",BLANK(),_1)3. Place [Total_Price_Measure] and [Running Total] in Viual, and click the SalesOrdelID column to sort by
4. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- EnrichedUser
Hi Anonymous
thank you for your time. I was able to test and modify these messures and did not run into the some memory issues as before. However, there was some problem for the running total calculating correctly.
From what I could see, the duplicated SalesOrderIDs would not correctly summarize.Cumulative Total Open Dollars =var Dollars = [Open Dollars]returnSUMX(FILTER(SUMMARIZE(ALLSELECTED(SalesOrders), SalesOrders[SalesOrderID],"@dollars", [Open Dollars]),[@dollars] >= Dollars ),[@dollars] )This is my current messure, but the processing time is still very slow at around 4 minutes.
avez vous trouvez la reponse a votre besoin je suis dans la mçeme situation que vous. je fais un total cumulée mais j'ai trop de lignes dans la table?
