Cumulative Amounts

[Guuske]

Hi,

This is my first my question so I hope I write it down correctly and yes I am inexperienced with Power BI. I have the following dataset:

Column A (index) / Column B (amount)

1                                 2

2                                -1

3                                -0.5

4                                -0.5

5                                3.5

What I would like to achieve is a third Column that shows me the som per row. Don't know how to say this in other words. With Excel this is an easy calculation hopefully it is the same with Power BI.

The result I would like to see:

Column A (index) / Column B (amount) / Column C (total)

1                                 2                                  2

2                                -1                                 1

3                                -0.5                               0.5

4                                -0.5                               0

5                                3.5                                3.5

What is the quickest way to achieve this?

[LigiaRoberts]

Hi,

Given that ISONORAFTER is not available when in direct query mode, is there a way around this ?

Best wishes

[Greg_Deckler]

Oh, wait, just use a Running Total Quick Measure:

Amount running total in Amount = 
CALCULATE(
	SUM('Amounts'[Amount]),
	FILTER(
		ALLSELECTED('Amounts'[Amount]),
		ISONORAFTER('Amounts'[Amount], MAX('Amounts'[Amount]), DESC)
	)
)

[Guuske · Accepted solution]

Thank you for the push in the right direction. While testing I made the following correction:

Amount running total in Amount =
CALCULATE(
 SUM('Table1'[Amount]);
 FILTER(
  ALLSELECTED('Table1'[Index]);
  ISONORAFTER('Table1'[Index]; MAX('Table1'[Index]); DESC)
 )
)

Thanks for your quick response.

[Greg_Deckler]

Well, I can tell you that it will probably involve the EARLIER function.