Running Total Dax help

[srinivas258]

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.

stop	total	running total
1	5	5
2	3	8
3	6	14

Thank you in advance.

[v-alq-msft]

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]

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]

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

[amitchandak]

@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]

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. 

[Anonymous]

Hi @srinivas258 

Please check out

https://stackoverflow.com/questions/18275270/dax-running-total-or-count-across-2-groups

Cheers

CheenuSing