Current Value should be based on SUM of 2 columns of above row

[SuperUser2]

Hello Everyone,

I am fairly new to Power Query, I want to acheive the following output, Appericiate if anyone can share a solution:

Thank you!

@Ahmedx 

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RihGlsokNEyk95dR_?e=zZQY8B

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SiZgljGQZQpmmQBZZmAWkGGuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Value 1" = _t, #"Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum (List.Range(
#"Added Index"[Value 1],0,[Index]))+#"Added Index"[Value 2]{0})
in
    #"Added Custom"

[SuperUser2]

Hey @Ahmedx 

Can you please help me with the following as well, I need running Sum the two columns 

[Ahmedx]

pls try this

List.Sum (List.Range(
#"Added Index"[Value 1],1,[Index])) + 
#"Added Index"[Value 2]{0}

[SuperUser2]

@Ahmedx 

Thank you for this. It works fine, but it goes to infinite loop of the row & data keeps on loading forever. my hunch is there should be a break clause to end the sumation. Can you please share an update?

[Ahmedx]

I don't know why it works like this for you.
maybe you have too many lines

[SuperUser2]

@Ahmedx 

Yes I do have more then 500,000 rows, I figured why it was going to loop. I am trying to use this caluse in else condition, instead of 224, it should be 9 + 0 Diagonally = 0, but it is showing 224?? Any ideas on this?

[Ahmedx]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RihzwLjnWwx6Htqm3?e=heBo45

[SuperUser2]

Hey @Ahmedx 
Thank you for getting back. For some reasons its not working for me, I have attached an excel to use as raw data, if you can pleaseeeee use this in your PBI, I will be really greatful.

https://1drv.ms/x/s!AgALyhOvtMpNgb50BM2kmBnEUs5D8g

[Ahmedx]

plse try this

if [Step] = 1 then [StepDuration] else [StepDuration] +
({null}&#"Added Index"[CummulativeDuration]){[Index]-1}

[SuperUser2]

@Ahmedx Were you able to load this in PBI, it keeps on Loading, i think goes into a loop... Can you pleaseeeeee try loading it?

[Ahmedx]

plse try this

https://1drv.ms/u/s!AiUZ0Ws7G26Rih9lEBQjNRmINc4L?e=IxxaH9

[SuperUser2]

Hey, @Ahmedx Thank you for getting back. In the above link, you have just reference Previous Row & sum it up with Current context, the previous row should be dynamic based on current context. It works on the sheet that i shared earlier but not works where there the condition is in multiple else. I have updated the sheet at the following link, if you can please help me out, i am stuck from couple of day trying to figure this out. Column E is the required result.

Sheet is available at the following link:

https://1drv.ms/x/s!AgALyhOvtMpNgb50BM2kmBnEUs5D8g

@Ahmedx 

@Ahmedx