looking for a simple running Total solution

[PeterBI]

Hello, 

i'm looking for a simple solution to get the running total like this Excel Solution. Thanks in advance.

Index	Saldo	Running Total
1	0	0
2	-87,5	-87,5
3	-225	-312,5
4	-237,5	-550
5	350	-200
6	-75	-275
7	0	-275
8	12,5	-262,5
9	50	-212,5
10	12,5	-200
11	-12,5	-212,5
12	50	-162,5
13	37,5	-125
14	-150	-275
15	450	175
16	-150	25

[Greg_Deckler]

http://www.daxpatterns.com/cumulative-total/

[PeterBI]

Thanks, i know your Site but every solution Group Data especially with the Date. I'm very Happy if there is a simple Way only maybe with the Index Row.

And please don't forget, I'm new to BI, so the first Steps are a little bit bumpy 😉

Regards, Peter

[ImkeF]

Hallo Peter,

you can do it in the query-editor instead, using M. Assuming your imported table sits in a step called "Source", you add a column where you fill in this formula:

(x)=> List.Sum(Table.SelectRows(Source, each [Index]<=x[Index])[Saldo])

You will then need to edit your code in the advanced editor and delete the "each" in the statement that has been generated:

The full code of your query would look like this then:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=> List.Sum(Table.SelectRows(Source, each [Index]<=x[Index])[Saldo]))
in
    #"Added Custom"

... or (x)=> List.Sum(Table.SelectRows(Source, (y) => y[Index]<=x[Index])[Saldo])

according to the discussion that is currently going on here re rolling averages (containing some links on M-syntax)

[Sean]

@PeterBI Try this! Just change the Table name

Running Total = CALCULATE(SUM(Table[Saldo]), FILTER (ALL(Table[Index]), Table[Index] <= MAX (Table[Index]) ) )

NOTE: Your table will HAVE TO BE SORTED by INDEX!

EDIT: => Use @ImkeF's answer below!

[PeterBI]

Hello Sean,

Thanks for your Idea.

This is the Formula with changed Names.

RT = CALCULATE (SUM(Trades[TradingErgebnisNetto]); FILTER (ALL(Trades[Index]); Trades[Index] <= MAX (Trades[Index]) ) )

There is no syntax error but the Row RT contain the same numbers as Saldo or new TradingErgebnisNetto. It ist the same error that i gete with my earlier solutions 😉 - so no running Total.

Data Type ist Dezimal

Thanks, Peter

[Sean]

@PeterBI

for this formula to work - the data HAS TO BE SORTED BY the INDEX Column =>1, 2, 3, etc....

So I would use @ImkeF's solution which you'll have to do in the Query Editor

[Greg_Deckler]

@Sean, I tried your solution because I had essentially come up with the same thing using SUMX but neither works as far as I can tell, basically you get the total sum for all of whatever it is you are summing. I expect that it is because the ALL clause and MAX clause always returns the MAX index and thus, everything.

I swear I've figured something similar to this out some other way. I always thought that there should be some way to do it with EARLIER as well, but have never figured that formula out either (or I have and forgotten).

[Sean]

@PeterBI @Greg_Deckler @ImkeF

Actually this seems to work in ANY order?

Pick any INDEX number - it gives you the Running Total up to that number - no matter how the table is sorted!

So the measure actually does exactly that - it doesn't look intuitive when you disrupt the order

But at each INDEX it gives you the Running Total up to that Index (<=MAX(Table1[Index])

[Greg_Deckler]

Ah, crucial bit of information left out, create as a measure, not a column, works like that, doesn't work as a column...

[PeterBI]

Hi smoupre,

Thanks for this solution. As a Measure it works perfect.

Unfortunatly, i need it as a calculated column.

Best Regards, Peter

[Greg_Deckler]

@PeterBI, @Sean, @ImkeF. Hey turns out that you can get there with EARLIER as long as you seed it the first value in your table:

[PeterBI]

Hi smoupre,

Thanks for your solution as a column.  is there any way, to use a Index instead of the date?

Thanks, Peter

[PeterBI]

Thanks, Table is sort ba index.

I will try Imkes solution tomorrow. It would be perfect if MS has a Function built in.

Thanks for your Help, Good Night.