Forum Discussion

srinivas258's avatar
srinivas258
Helper I
6 years ago

Running Total Dax help

hello everyone,

I am trying to do basic running total in power bi.  Its based on Stop I have to do the total. Any help/suggestion please on this.

 

stoptotalrunning total
155
238
3614

 

Thank you in advance.

 
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    srinivas258 ,

    Try like

    CALCULATE(SUM(Table[Total]),filter(all(Table),Table[stop] <=max(Table[stop])))

    Or
    CALCULATE(SUM(Table[Total]),filter(allselected(Table),Table[stop] <=max(Table[stop])))

    • srinivas258's avatar
      srinivas258
      Helper I
      6 years ago

      Thanks amitchandak. i tried this and its working CALCULATE(SUM(Table[Total]),filter(allselected(Table),Table[stop] <=max(Table[stop])))

       

      but only other problem is if I add an other column (lets say ColumnA) from different table(lets say TableA) it stopped working.

      Can you please guide me how I can add that column and table to above formula. 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, srinivas258 

     

    Based on your description, I created data to reproduce your scebario.

    Table:

     

    You may create two measures as below.

     

    Total = 
var _stop = SELECTEDVALUE('Table'[Stop])
return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Stop]<=_stop
    ),
    'Table'[Stop]
)

Running Total = 
var _stop = SELECTEDVALUE('Table'[Stop])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Total",
    CALCULATE(
        SUM('Table'[Stop]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Stop]<= EARLIER('Table'[Stop])
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Runnung Total",
    var t = [Total]
    return
    SUMX(
        FILTER(
            tab,
            [Total] <= t
        ),
        'Table'[Total]
    )
)

return
SUMX(
    FILTER(
        newtab,
        [Stop] = _stop
    ),
    [Runnung Total]
)

     

     

    Result:

     

    I wonder what the column from another table is like and if there is a relationship between two tables.

     

    Best Regards

    Allan

     

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

     

    • srinivas258's avatar
      srinivas258
      Helper I
      6 years ago

      Hi v-alq-msft , amitchandak ,

       

      Apologies for not being clear. Stop and Total are from same table. I have an other column (Stop Description) from other table and I would like to add that to this Running Total table. And there is join in place between these two tables. I tried using lookupvalue for had no luck.

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, srinivas258 

         

        Could you please show some sample data and the expected result with OneDrive for business? Do mask sensitive data before uploading. Thanks.

         

        Best Regards

        Allan