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 wondering if there is a more efficient way. I've read most posts on Running totals, and avoided using calculate vs sumx due to context transition but still. 

 

Table structure as follows

 

QtySKUStartEndIDResult
10A5/1/20196/1/2019A10
-10A5/2/20196/1/2019A0
40A5/2/20196/1/2019B40

 

Here is my Calculated Column

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

 

 

 

 

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.

3 Replies

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

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