Conditional sum based on next row

[Anonymous]

Hi,

I have a requirement to calculate the total for the current row based on the next rows using DAX :

Data Set :

Type	Value	Rule	Expected Result
A	10	B + C +D +E	250
B	20	C + D + E	230
C	50	D + E	180
D	80	E	100
E	100	E	100

How I can solve this .

Thanks.

[amitchandak]

@Anonymous , if the type or value are incremental in nature try a new column like below. Or add an index column in power query and use that https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

new column = sumx(filter(Table, [Type] > earlier([Type]) , [Value])

or

new column = sumx(filter(Table, [Value] > earlier([Value]) , [Value])

or

new column = sumx(filter(Table, [index] > earlier([index]) , [Value])

[Anonymous]

both the columns are not incremental , column (Type) is a phase & it has to be in sequence . 

[amitchandak]

@Anonymous , Better to an index column in power query and try, the last formula. I shared a link for index column, if needed

[Anonymous]

Its working now for all scenario's , i've added IF condtion on top your formula:

=

IF (

MAX(Sheet1[Index]) = Sheet1[Index],

Sheet1[TotalCount],

SUMX(FILTER(Sheet1,Sheet1[Index] > EARLIER(Sheet1[Index])),Sheet1[TotalCount]

)

)

[Anonymous]

I did add the index column , its working fine till the 2nd last row , I need to repeat the last row value as is , how I can I do that ? 

I used the below formula :

new column = sumx(filter(Table, [index] > earlier([index]) , [Value])

[amitchandak · Accepted solution]

@Anonymous , change like

new column = if( table[index] < max(table[Index]), sumx(filter(Table, [index] > earlier([index]) ) , [Value]) ,table[Index])

Sorry, I did not notice that

[Anonymous]

yep its wokring fine , thanks alot .