Re: Running Total Non Date - Not Enough Memory

EnrichedUser · ‎02-26-2021

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.

Open Dollars =
CALCULATE(SUM(SalesOrders[TotalPrice]), SalesOrders[Status] = "Open")

Cumulative Open Sales Test 1 =

VAR IndexRank = 'Ranking Index'[Ranking Index Value]

RETURN

SUMX(

FILTER(

SUMMARIZE(SalesOrders, SalesOrders[SalesOrderID],

"Sales", [Open Dollars],

"Sales Ranking", RANKX(ALLSELECTED( SalesOrders), [Open Dollars],, DESC)),

[Sales Ranking] <= IndexRank),

[Sales] )

Cumulative Open Sales Test 2=

VAR RANKING =

RANKX(VALUES(SalesOrders[SalesOrderID]), [Open Dollars],,DESC,Dense)

VAR RunningTotal =

CALCULATE([Open Dollars], SalesOrders[Status] = "Open",

FILTER(VALUES(SalesOrders[SalesOrderID]),

RANKING >= RANKX(

VALUES(SalesOrders[SalesOrderID]),

[Open Dollars],,

DESC,Dense)

)

RETURN

RunningTotal

yenkk · ‎11-21-2023

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?

Anonymous · ‎03-01-2021

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 · ‎03-01-2021

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]

return

SUMX(

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.

Ashish_Mathur · ‎02-26-2021

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

EnrichedUser · ‎03-01-2021

HI @Ashish_Mathur

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

Anonymous · ‎03-02-2021

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.

EnrichedUser · ‎03-03-2021

Hi @Anonymous, Thank you again and you are right. It looks like I misstyped 350 for 300.

I have tried this method again and am looking into further optimizing my reports. Unfortunately, I am still not able to quickly populate the Running Total Messure and even the Total_Price_Messure takes a great deal of time. The issue may be somewhere else in my file, but these are the only portions of my entire report that has a processing problem with all other messures under 500 ms and the top across all reports less than 1400 ms.

Anonymous · ‎03-04-2021

Hi @EnrichedUser ,

The speed on power bi Desktop is slow, and the solution seems to be only dax optimization and reduction of useless data. You can also put the data into the SQL server and use Direct Query to input data, which will be faster

You can use DAX Studio to optimize the inspection of DAX statements from a performance perspective and use the Performance analyzer to check the performance of each of your report elements when users interact with them, and which aspects of performance consume the most (or least) resources

This is a link to related content, I hope it will help you:

https://daxstudio.org/

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

Best Regards,

Liu Yang

EnrichedUser · ‎03-04-2021

I will look into the dax studio.

My guess is going to be that I have to optimize the code for the running total which is what I am looking for: an optimized running total dax solution.