Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Remaining values calculation

I want to add an additional row for "Remaining Commercial" that subtracts Commercial 1 and Commercial 2 from Total Commercial to avoid double counting (i.e. Remaining Commercial would read "$24,265,334" and I could take out Total Commercial from the table). 

 

Is there a formula or data modeling tool I can use to accomplish this?

 

 

 

Thanks! 

Need Help
Tutorial Requests

1 Reply

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi juliebenedict,

    It seems that you want to add a row in data  model, right? If so, you could try to use below M code to see whether it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcoopNTAwMvNVMFTSUTIyhxCxOkgSRkAxE1MQYYkqYQwUswBJmJuAJZzzc3NTi5IzE3PAhhmbgAhLdCmQcaYgraamYKmQ/BKgKEIBUMbQ0AhMWijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, Cur = _t, YAGO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Cur", Int64.Type}, {"YAGO", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "group name", each Text.Combine(List.RemoveItems(Text.ToList([Account]),{"0".."9"}))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"group name"}, {{"sum cur", each List.Sum([Cur]), type number}, {"sum yago", each List.Sum([YAGO]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([group name] <> "B&M ")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [sum cur]{[Index]}- #"Added Index" [sum cur]{[Index]-1} otherwise 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each try #"Added Index" [sum yago]{[Index]}- #"Added Index" [sum yago]{[Index]-1} otherwise 0),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each "remaining"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"group name", "sum cur", "sum yago", "Index"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Custom] <>0)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Custom", "Cur"}, {"Custom.1", "YAGO"}, {"Custom.2", "Account"}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Renamed Columns"})
in
    #"Appended Query"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.