New column that subtracts row values based on another column

[samwar14]

I'd like to review the improvement in test scores for each students based on the test stage, how do I create the column Improvement below where the Improvement = score at initial assessment - score at completed programme (and where they have't completed programme then 0) ? 

I've seen some examples using the add custom column but they don't make sense and the syntax isn't accepted when I try to add to the Custom column formula box.

Any help appreciated.

Thx

[samwar14]

Hi ImkeF, thanks for your suggestion. The fabric community emails went straight to my junk folder so I didn't see your reply. I deciced to make the calculation within the SQL query and then imported it in the main table. Yes there is always a max. 2 per studen and your solution seems interesting. I might give it a go as an alternative just out of interest. Thanks again.

[ImkeF]

Hi @samwar14 ,
if there is alway max. 2 values per student, then you could:
1) pivot on [Stage] with no aggregation on value column [Score]

2) Fill down in column [Score]

This will return the starting values in all rows with ending values.

3) Add custom column where you subtract the starting value from the ending value 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIPbMyyxRitWBCBoBsQkQV4BFQNKmQGyArMwIqg9D0ASqGqg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Score = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Score", Int64.Type}, {"Column5", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column5]), "Column5", "Score"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Init"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Filled Down", "Subtraction", each [x] - [Init], type number)
in
    #"Inserted Subtraction"