Subtotal of Negative Values

Hi  Nathaniel,

Thank you for your reply please find the details and let me know if you are able to understand my query.

Yes need to split the single to get the output.

Hi jhaanand81 ,

Based on your description, you can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZdJduQwCEDvknVKj3k4S17uf42m2rJLsul17/IgX4wG6ufni/GF8CIg/Pr+Infmofj1+33TvIwAE2BgPJWGxQUOkkMHL8gLdBeDYfpXRbyo0nASsJlCE3Ad7AciC1IqIvqfDLioj+yYekzbeMgNdaQ1zDvjcDCyMizCMOZz5AtSmpnMnUBlRRqBT6RUpp0VNNbEnjGumjZMUiAMsCdSmuSGoAzGHNJYKZWGdZ6FqOCAaDwLcWgZEy3XsmOsktZFUwXQtKuefqsNYhcPWooO5CYedORs7QhljqTOjkhrJwJABjeZLk10mUZSYxneeFYqF++i4ar04KbTSmPTL906ICThKmesHVDVzIaoDw3jCn4lSnNGshGVRwwd0CDvFJ+duTMoXDl2bxgUnW/dYkmRGmrSxJJKRyxIu5X082Nanyp5qDeApGSTRkmdKby9L6DUuFpyk2gdYplNcnOIlTuHgqDLXxDONkS7DRaq6s1PEWI1ocyqHcOMaAOkYZiJO6YSkjqsMSNZbXIQq/jFSeky4sgWrkOXs8wcScHco6EaOqOpbGmEuUO4/rKzF3emUiOzeWCvYgT6gJlQ3goZORvoxmjVSwZqw6jqOW/+3b9bdj79uxNoNSbz3NUbUio7J+7OAGnWkMCGqdREa4cjhM6Pfmc40rxjxPA9vzpGjD57arNToy2GPxNaGnZrEXCj66vfGQjPhqlv/j3C7ImUJjortYzks1tXojQx/b31ZpDSmIuK9uYMPo3vjNfE/0zjjXGTcxnujNRr187bkGqNzjMmlxhzLG1Eac4Qt+ETgNKcii82UK7tmfJUsnpdSjSEnzp6J+2kNlNZV1fTtDV/znPk5lltwhnjOn+qu/JzBmzzyqiTvw/iTl43CDQdtHyPq7huQ+oer/UXjdypxlQjrxP8s8LXd67b4uZkLf3GGynptXLXtkAkbuRUvxekkQdf42qVL+feLl9yvw+KahZt5LXbuPn/CJ8zamtfqrPI4/xtspst8e8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Date" = _t, #"Amount in doc. curr." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Date", type text}, {"Amount in doc. curr.", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Document Date", type date}}, "en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [#"Amount in doc. curr."] > 1),
    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Amount in doc. curr."}, {"Added Index1.Amount in doc. curr."}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.Amount in doc. curr.", "Output1"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [#"Amount in doc. curr."] < 0),
    #"Added Index2" = Table.AddIndexColumn(#"Filtered Rows1", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Index"}, #"Added Index2", {"Index.1"}, "Added Index2", JoinKind.LeftOuter),
    #"Expanded Added Index2" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index2", {"Amount in doc. curr."}, {"Added Index2.Amount in doc. curr."}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Added Index2",{{"Added Index2.Amount in doc. curr.", "Ouput2"}}),
    BufferedValues = List.Buffer(#"Renamed Columns1"[Output1]),
    CumulativeTotal = Table.AddColumn(#"Renamed Columns1", "Output3", each -16197615.03 + List.Sum(List.FirstN(BufferedValues,[Index])),type number),
    #"Removed Columns" = Table.RemoveColumns(CumulativeTotal,{"Index"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.