Forum Discussion

GBKYE2's avatar
GBKYE2
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • FarhanAhmed's avatar
    FarhanAhmed
    4 years ago

    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 )))

     

     

     

     

FarhanAhmed's avatar
FarhanAhmed
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
GBKYE2
Frequent Visitor
4 years ago

Thanks for your help FarhanAhmed, this worked perfectly.