Forum Discussion
Calculate delta per row
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!
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 )))
4 Replies
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.
FarhanAhmedCommunity Champion
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 )))Thanks for your help FarhanAhmed, this worked perfectly.
