Forum Discussion

renegar's avatar
renegar
Microsoft Employee
6 years ago
Solved

A more efficient running total ?

I've calculating a running total based on 4 conditions/filters, the calculated column works on a small scale, but when I used in larger data set (2M rows) it never finished recalculating. I'm wonderi...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    renegar ,

     

    SUMX() is an iterator function so it will traverse the whole table, you can use SUM() function instead as below to improve the performance.

    Result =
VAR start1 = Table1[Start]
VAR end1 = Table1[End]
VAR sku1 = Table1[SKU]
VAR subid1 = Table1[ID]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            Table1,
            [Start] <= start1
                && [End] = end1
                && [SKU] = sku1
                && [ID] = subid1
        )
    )

    Community Support Team _ Jimmy Tao

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

v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

renegar ,

 

SUMX() is an iterator function so it will traverse the whole table, you can use SUM() function instead as below to improve the performance.

Result =
VAR start1 = Table1[Start]
VAR end1 = Table1[End]
VAR sku1 = Table1[SKU]
VAR subid1 = Table1[ID]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            Table1,
            [Start] <= start1
                && [End] = end1
                && [SKU] = sku1
                && [ID] = subid1
        )
    )

Community Support Team _ Jimmy Tao

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