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.

hnguy71's avatar
hnguy71
Super User
6 years ago

Hi renegar ,

Instead of using a calculated column, perhaps a measure may suffice? For example, a simple cumculative total measure like this would give you a similar result (for the year):

RunningTotal = CALCULATE(SUM(YOUR_TABLE[Qty]), DATESYTD(YOUR_TABLE[Start]))

 

renegar's avatar
renegar
Microsoft Employee
6 years ago

Unfortunatetly I can't but, I found that if I do that "[End]=end1" comparison in text (rather than date) it runs pretty fast. Go figure...