Running Total grouped on a measure

[RNair]

@MFelix  and others,

I have attached a pbix file at the onedrive location Below.

https://1drv.ms/u/s!Aqc2e7y3QZN5mFH_F1YA9aJ7q1le?e=J2Y78g

The 1st 4 columns are raw data. SeriesID is a measure. Now I want to create a running total on Value but grouped by SeriesId.

So the result will look like this.

I am needing to create last column as a measure.

regards,

Ramesan

[amitchandak]

@RNair , Try a formula like

Cumm Value = CALCULATE(SUM(Table[Value]),filter(allselected(Table),Table[date] <=max(Table[Date]) && Table[SeriesId] =max(Table[SeriesId]) ))

[RNair]

@amitchandak ,

Amit, Thanks for this. The problem is that SeriesId is a measure so will not be accepted as an argument for the MAX function.

Regards,

Ramesan 

[amitchandak]

@RNair , share the formula of SeriesId 

[RNair]

@amitchandak 

From Qty column you calculate a running total called QtyAft. Sometimes the QtyAft becomes zero.  

QtyAft = CALCULATE(sum(Sheet1[Qty]), FILTER(ALLSELECTED(Sheet1), Sheet1[Date]<= MAX(Sheet1[Date])))

Initially SeriesID is 1. Whenever the QtyAft reaches 0, Series ID changes for the next transaction.

So SeriesID is

SeriesID =

VAR ListOfZeros = FILTER(ALLSELECTED(Sheet1), [QtyAft] = 0 && Sheet1[Date] <= MAX(Sheet1[Date]))

Return

IF([QtyAft]= 0, CountRows(ListOfZeros), CountRows(ListOfZeros) +1 )

 Regards,

Ramesan

[RNair]

@amitchandak , @MFelix  and others,

I was able to create the output I needed by using a variable.

Output required received as follows.

 The measure **bleep** Value by Series was defined as follows.

**bleep** Value By Series =

VAR Series = [SeriesID]

Return

CALCULATE(SUM(Sheet1[Value]), FILTER(ALLSELECTED(Sheet1), Sheet1[Date] <= MAX(Sheet1[Date]) && [SeriesID] = Series))

Thank you for all the support. Feedback received in the forum has helped me to get to the solution.

Regards,

Ramesan

[MFelix · Accepted solution]

Hi @RNair ,

Glad you were abble to get it. Just for context what is happening is that when you use variable in a calculation that value stays fixed in this case when you make the calculation and you are picking up the SeriesID what in fact you are doing is sayin to keep the number fixed. The calculation first picksup SeriesID and then compares that "fixed value" with the one on the SeriesID that is calculated on row context.