Calculate delta per row

[GBKYE2]

Hi all,

I'm trying to do something which should be pretty basic but I can't seem to wrap my head around how to. Simply, I would like to automate the Delta column from the below example:

Prod	Test Case	Value	Delta from previous Sim
a	Default	5	0
a	Sim2	6	1
a	Sim3	5	-1
b	Default	3	0
b	Sim2	8	5
b	Sim3	7	-1
c	Default	4	0
c	Sim2	6	2
c	Sim3	8	2

My source data selects from the latest file in a folder and the issue is that each file can have a different number of Test Cases( e.g. my example here has 3 cases per product, another file could have 15 per product) and I want to be able to dynamically adjust rather than nest IF or use Switch.

Any suggestions? Thanks!

[FarhanAhmed · Accepted solution]

I think you need to have a index/sequence number on how to calculate delta here. means

default is 1

Sim2 is 2

Sim3 is 3

one way is you can create it by  Group By in power query for Test Cast and operation shoud be "All Rows"

Then add index on it

Expand the table again

Create a calculated column like this

_Delta = Var P = delta[Prod]
Var v = delta[Value]
Var i = delta[Index]
 RETURN

 IF ( delta[Index]=0,0, v- CALCULATE(MAX(delta[Value]),FILTER(delta,delta[Prod]=P &&  delta[Index]=i-1 )))

[GBKYE2]

Thanks for your help @FarhanAhmed, this worked perfectly.

[tamerj1]

@GBKYE2 
Do you have any index or date column or this is just your data? I mean are the values listed in the Test Case column are real? Like starting from "Default" then "Sim1", "Sim2", Sim3', "Sim4, ...... and so on?

[GBKYE2]

Hi @tamerj1, no date column but I followed @FarhanAhmed's advice above and added an index per simulation in Power Query and was able to iterate off that.