Running total on a large scale dataset

[xieli1999]

Hi all,

I'm having a 80 million record dataset. When I was doing the POC report that has only account number, I used the following fomular to generate my balance: 

Balance = [Opening balance] +

    CALCULATE(

        SUM(transaction[Amount]),

        FILTER(

            ALLSELECTED(transaction),

            [Index] <= MAX(transaction[Index])

        )

    )

The above fomular works exactly as what I want. However, when I load not all my data, but just over 10 million data, this fomular gives me an error message saying that it's running out resources.

Then I thought about dividing my original transaction table into pieces first as for my report, anyway, one account number and only one account number must have been selected. I used the following fomular to divide my table:

FilteredTable =

CALCULATETABLE(

    'transaction',

    FILTER (

        'transaction',

        'transaction'[Account_number] = SELECTEDVALUE('transaction'[Account_number])

)

)

It gives me an empty table, if I replaced "SELECTEDVALUE('transaction'[Account_number])" by a fixed text string, new table will be created with data.

Anybody can help me on this issue?

Thanks in advance.

[xieli1999]

We have a table that has all client's transaction since they become our client. Sometimes, people would ask us to generate a statement of account for a particular client.

[lbendlin]

how does that require a running total?  And how fast does it have to be?

[v-yohua-msft]

Hi, @xieli1999 

An error message indicating resource exhaustion can be due to the complexity and size of the dataset processed by the formula. Power BI has certain limitations when working with large datasets, especially when it comes to memory usage.
Use variables to break down formulas into smaller, manageable pieces. This improves readability and performance. Ensure that their use is absolutely necessary and cannot be simplified.

The problem with creating a filter table that returns an empty can be due to the way the function is used. When there is a value in the specified column in the current context, a value is returned; Otherwise, it will return a blank. This may mean that your slicer or filter context doesn't have a single selected account at the time of calculation

Make sure that the slicer or filter settings allow for a single selection, or that the filter context that returns a non-null value is applied correctly. 

Temporarily replace with a hard-coded account number (as you've done) to confirm that the rest of the formula is working as expected. This helps to isolate the problem to the dynamic selection part of the formula.
If possible, review and optimize the performance of your data model. This could include reducing the number of columns, ensuring proper indexing, or breaking down data into smaller, more manageable tables.

For further optimization techniques and to understand limitations, see the following resources:

Optimization guide for Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @xieli1999 

Please try

Balance =
VAR OpenBslsnce = [Opening balance]
VAR IndexAmount =
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( transaction[Index] ), ALLSELECTED () ),
"@Amt",
CALCULATE (
SUM ( transaction[Amount] ),
ALLEXCEPT ( transaction, transaction[Index] )
)
)
VAR Amount =
SUMX (
WINDOW ( 0, abs, 0, rel, IndexAmount, ORDERBY ( transaction[Index], ASC ) ),
[@Amt]
)
RETURN
OpenBalance + Amount

[xieli1999]

Thans tamerj1 for your solution, unfortunately when I applied it on a 20 milliaon record table, it's again running out of resources and my actual table has over 80 million records.

[lbendlin]

a running total over 80 million records seems a bit excessive.  What is the business question you are trying to answer?