Forum Discussion

TK12345's avatar
TK12345
Icon for Resolver II rankResolver II
3 years ago
Solved

Cumulative sum with conditons and reset sum

Hi all, 

I have a question I need to visualise in Power BI and want to do this by using the Power Query. I need to have the column Expected Outcome. This one is based on if the status equals Binnen then amount, as long as the next row has the status Binnen then the sum needs to be cumulative. If it is Buiten the cumulative sum needs to reset to 0 and start all over.  Besides that we have multipe customers. So for each customer we need a new cumulative sum. I think I need to use the sorting for Date and Customer. And need indexes, but that only works for 2 rows insteat of cumulative. 

What do I need to do to solve this?

DateDocumentCustomerAmountStatusEXPECTED OUTCOME
1-1-2020factuur123,35Binnen23,35
2-1-2020factuur134,34Binnen57,69
3-1-2020factuur1155,99Binnen213,68
4-1-2020factuur1203,87Binnen417,55
5-1-2020credit1270,19Binnen687,73
6-1-2020factuur156,89Buiten0
7-1-2020factuur110,53Binnen10,53
8-1-2020factuur1125,14Binnen135,67
9-1-2020factuur1129,46Binnen265,13
10-1-2020factuur1133,77Binnen398,90
1-1-2020factuur2138,09Binnen138,09
2-1-2020factuur2142,40Binnen280,49
3-1-2020factuur2146,72Buiten0
4-1-2020factuur2151,04Binnen151,04
5-1-2020credit2155,35Buiten0
6-1-2020factuur2159,67Binnen159,67
7-1-2020factuur2163,98Binnen323,65
8-1-2020factuur2168,30Binnen491,95
9-1-2020factuur2172,61Binnen664,56
10-1-2020factuur2176,93Binnen841,49
Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, TK12345 

    let
    text_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdI5CsMwEAXQu6j+gVm0trmGSRGygBsXwb5/jJSQSEjgQkjFYxD/zzQZPvFJSMjAPK+3ddte+4v3Iwp1+32el+WxmAsmIyOrFmprqyPLziGlGtvhJ0gRQ43dD99ej/u8fm0gcDPYjwY7j5jtNq8fG4Y/Jjit58ahFQduokhjnGB9jZmGWhWhyaLbnmQcQU0Y3foytgJLB/or2CNIHV23v4wdg5o4ev3JZzHKxv0Gd/srOME3YXQLzNgrUjzQYMER2oTRbTDjIPB8pMGiPdL/Jl3e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Document = _t, Customer = _t, Amount = _t, Status = _t]),
    amount_type = Table.TransformColumnTypes(text_table, {{"Amount", Currency.Type}}, "de-DE"),
    Source = Table.TransformColumns(amount_type, {"Date", each Date.FromText(_, [Format="M-d-yyyy", Culture="de-DE"])}),
    fx_outcome = (tbl as table) =>
        let 
            amounts = List.Buffer(tbl[Amount]),
            sts = List.Buffer(tbl[Status]),
            positions = List.Buffer(List.Skip(List.Positions(sts))),
            column_names = Table.ColumnNames(tbl) & {"outcome"},
            outcome =
                List.Accumulate(
                    positions,
                    {if sts{0} = "Buiten" then 0 else amounts{0}},
                    (s, c) => s & {if sts{c} = "Buiten" then 0 else amounts{c} + List.Last(s)}
                ),
            final = Table.FromColumns(Table.ToColumns(tbl) & {outcome}, column_names)
        in final,
    groups = Table.Group(Source, {"Customer"}, {{"all", each fx_outcome(Table.Sort(_, {"Date"}))}}),
    out = Table.Combine(groups[all])
in
    out

2 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    Hello, TK12345 

    let
    text_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdI5CsMwEAXQu6j+gVm0trmGSRGygBsXwb5/jJSQSEjgQkjFYxD/zzQZPvFJSMjAPK+3ddte+4v3Iwp1+32el+WxmAsmIyOrFmprqyPLziGlGtvhJ0gRQ43dD99ej/u8fm0gcDPYjwY7j5jtNq8fG4Y/Jjit58ahFQduokhjnGB9jZmGWhWhyaLbnmQcQU0Y3foytgJLB/or2CNIHV23v4wdg5o4ev3JZzHKxv0Gd/srOME3YXQLzNgrUjzQYMER2oTRbTDjIPB8pMGiPdL/Jl3e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Document = _t, Customer = _t, Amount = _t, Status = _t]),
    amount_type = Table.TransformColumnTypes(text_table, {{"Amount", Currency.Type}}, "de-DE"),
    Source = Table.TransformColumns(amount_type, {"Date", each Date.FromText(_, [Format="M-d-yyyy", Culture="de-DE"])}),
    fx_outcome = (tbl as table) =>
        let 
            amounts = List.Buffer(tbl[Amount]),
            sts = List.Buffer(tbl[Status]),
            positions = List.Buffer(List.Skip(List.Positions(sts))),
            column_names = Table.ColumnNames(tbl) & {"outcome"},
            outcome =
                List.Accumulate(
                    positions,
                    {if sts{0} = "Buiten" then 0 else amounts{0}},
                    (s, c) => s & {if sts{c} = "Buiten" then 0 else amounts{c} + List.Last(s)}
                ),
            final = Table.FromColumns(Table.ToColumns(tbl) & {outcome}, column_names)
        in final,
    groups = Table.Group(Source, {"Customer"}, {{"all", each fx_outcome(Table.Sort(_, {"Date"}))}}),
    out = Table.Combine(groups[all])
in
    out
  • Adamboer's avatar
    Adamboer
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    To achieve the expected outcome in Power BI, you can follow these steps:

    1. Import the data into Power BI and load it into the Power Query Editor.
    2. Sort the data by date and customer by right-clicking on each column header and selecting Sort Ascending.
    3. Add a custom column by clicking on the Add Column tab and selecting Custom Column. Name the column "Cumulative Sum."
      1. Add another custom column named "Expected Outcome" and use the following formula:In the formula bar, enter the following formula:

        if [Status] = "Binnen" then List.Accumulate(
        Table.Range(#"Sorted Rows", 0, [Index]),
        0,
        (state, current) => state + current[Amount]
        )
        else 0


        This formula creates a cumulative sum for each row where the status is "Binnen" and resets to 0 for each row where the status is "Buiten." 5. Expand the custom column by clicking on the expand icon next to its header and selecting the "Cumulative Sum" column.

    if [Status] = "Binnen" then [Cumulative Sum]
    else 0

     

    This formula sets the Expected Outcome column equal to the cumulative sum for each row where the status is "Binnen" and 0 for each row where the status is "Buiten." 7. Close and apply the Power Query Editor changes and create a table visualization in Power BI with the columns Date, Document, Customer, Amount, Status, and Expected Outcome.

    This should result in a table with the expected outcome values calculated as desired, with the cumulative sum resetting to 0 for each row where the status is "Buiten" and calculated separately for each customer.