Calculating value from multiple rows with multiple delimiting variables

[Ryan ODonnell1705507824]

Looking to calculate a % difference (lift) between two different rows, which I believe I can do easily enough with an OVER function. My problem arises when trying to apply this to the appropriate rows, taking into account some additional criteria. In the attached picture I've laid out a simplified version of what I'm trying to accomplish, with colF being the desired calculated column to be created with the highlighted values.I need to make sure the % difference is calculated for every Net Population, for the specific Version, Granularity, and Project Code variables. I'm sure there is a fairly straightforward way of doing this (maybe the Previous, or Intersect functions). Any advice or feedback would be greatly appreciated. Thank you!

[Ryan ODonnell1705507824]

figured this out, ended up essentially created two helpiercolumns and then applied an over - intersect - previous

 

created two helper fuctions

 

rank-test is simply control=1, net=2, treatment=3

identifier-row is concat of the identifying variables i want to calculate across

 

 

case when [population]="Treatment" then null

when [Final Rate] / First([Final Rate]) over (Intersect([identifier-row],Previous([rank-test]))) end