Forum Discussion

EnrichedUser's avatar
EnrichedUser
Icon for Helper III rankHelper III
5 years ago

Running 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 n...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    EnrichedUser
    Icon for Helper III rankHelper III
    5 years ago

    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.