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

     

     

     

     

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

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

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.