none
Runnig total in power Query .

    Question

  • I need to calculate running total in power query based on a field  "Final Time" for which "Lot Id" field has same values.When the Lot Id changes , the running total should start from the scratch. Can anyone help to solve this. I should get the running total as stated in below example.

     Lot Id      Final Time   Running Total

      1               0.5              0.5

      1               1.2              1.7

      1                0.7             1.9

      2                0.3             0.3

      2                0.5             0.8 

      Thanks in advance

    Tuesday, June 9, 2015 5:29 AM

Answers

  • Some variation needed to apply it on LotId-Level (Product on my example below):

    let
       // For the general principle see: http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
     Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
       // Variation needed for applying it on ProductID-level
     Grouped = Table.Group(Source, {"Product"}, {{"Count", each Table.RowCount(_), type number}}),
       RT = (Prod)=>
       let
       Filter=Table.SelectRows(Source, each [Product] = (Prod)),
       AddIndex = Table.AddIndexColumn(Filter, "Indeks", 1, 1),
       List = List.Buffer(AddIndex[Sale]),
       RT = Table.AddColumn(AddIndex, "Running Total", each List.Sum(List.FirstN(List,[Indeks])))
       in
       RT,
     RunningTotal = Table.AddColumn(Grouped, "RT", each RT([Product])),
     Expand = Table.ExpandTableColumn(RunningTotal, "RT", {"Date", "Product", "Sale", "Indeks", "Running Total"}, {"Date", "Product.1", "Sale", "Indeks", "Running Total"})
    in
     Expand


    Imke

    Tuesday, June 9, 2015 8:26 AM
    Moderator
  • Hi :-)

    Sorry Gill but it is not good way (efficiency is very poor) :-(

    Try this one below. You can choose function you want to use (fxRunningTotal or fxRunningTotalLG) and check the difference.

    let
    // function based on List.Sum(List.FirstN
        fxRunningTotal = (tab as table, col_name as text) as table =>
    
        let
           AddIndex = Table.AddIndexColumn(tab, "Indeks", 1, 1),
           List = List.Buffer(Table.Column(AddIndex,col_name)),
           RunningTotal = Table.AddColumn(AddIndex, "Running Total", each List.Sum(List.FirstN(List,[Indeks]))),
           RemCol = Table.RemoveColumns(RunningTotal,{"Indeks"})
        in
           RemCol,
    // function based on List.Generate
        fxRunningTotalLG = (tab as table, col_name as text) =>
          
          let
             LTS = List.Buffer(Table.Column(tab, col_name)),
             ListCount = List.Count(LTS),
             LstGen = List.Generate(()=>
                    [Counter=1, RT= LTS{0}],
               each [Counter]<=ListCount,
               each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
               each [RT]),
             LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total"}}),
             TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
          in
             TblToCol,
    
    // choose column to grouping and to sum
        CFT = "Lot Id", // column name for grouping
        CFRT = "Final Time", // column name to sum
    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GrupCol = Table.Group(Source, {CFT}, {{"tab", each _, type table}}),
        RemCol = Table.SelectColumns(GrupCol,{"tab"}),
    // You can choose function fxRunningTotalLG or fxRunningTotal - arguments are the same
        TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),
        ExpCol = Table.ExpandTableColumn(TransfCol, "tab", {"Lot Id", "Final Time", "Running Total"}, {"Lot Id", "Final Time", "Running Total"})
    in
        ExpCol
    
    Regards :-)

    Tuesday, June 9, 2015 2:01 PM

All replies

  • Here is a recommended blog post by Ken Puls that can help you to calculate it with Power Query.
    Tuesday, June 9, 2015 7:34 AM
  • Some variation needed to apply it on LotId-Level (Product on my example below):

    let
       // For the general principle see: http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
     Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
       // Variation needed for applying it on ProductID-level
     Grouped = Table.Group(Source, {"Product"}, {{"Count", each Table.RowCount(_), type number}}),
       RT = (Prod)=>
       let
       Filter=Table.SelectRows(Source, each [Product] = (Prod)),
       AddIndex = Table.AddIndexColumn(Filter, "Indeks", 1, 1),
       List = List.Buffer(AddIndex[Sale]),
       RT = Table.AddColumn(AddIndex, "Running Total", each List.Sum(List.FirstN(List,[Indeks])))
       in
       RT,
     RunningTotal = Table.AddColumn(Grouped, "RT", each RT([Product])),
     Expand = Table.ExpandTableColumn(RunningTotal, "RT", {"Date", "Product", "Sale", "Indeks", "Running Total"}, {"Date", "Product.1", "Sale", "Indeks", "Running Total"})
    in
     Expand


    Imke

    Tuesday, June 9, 2015 8:26 AM
    Moderator
  • In here is my version (inspired from the same source:  http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/).

    Assume that in your Excel worksheet Table1 contains "Lot Id" and "Final Time" as above.

    First you should create the following function query, and name it "GetRunningTotal".

    (source as table, id as number, rowNum) =>
    let
        #"Changed Type" = Table.TransformColumnTypes(source,{{"Lot Id", Int64.Type}, {"Final Time", type number}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Lot Id] = id)),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Index] <= rowNum),
        #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Lot Id"}, {{"RunningTotal", each List.Sum([Final Time]), type number}}),
        RunningTotal = #"Grouped Rows"{[#"Lot Id"=id]}[RunningTotal]
    in
        RunningTotal
    Next - Create the following query -

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lot Id", Int64.Type}, {"Final Time", type number}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Running Total", each GetRunningTotal(Table.Range(#"Added Index", 0, [Index]), [Lot Id], [Index])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"


    Imke, Please let me know if my query is less efficient :)

    Tuesday, June 9, 2015 10:50 AM
  • Thanks Imke .. Its working perfectly now!!!!

    Tuesday, June 9, 2015 12:44 PM
  • Hi :-)

    Sorry Gill but it is not good way (efficiency is very poor) :-(

    Try this one below. You can choose function you want to use (fxRunningTotal or fxRunningTotalLG) and check the difference.

    let
    // function based on List.Sum(List.FirstN
        fxRunningTotal = (tab as table, col_name as text) as table =>
    
        let
           AddIndex = Table.AddIndexColumn(tab, "Indeks", 1, 1),
           List = List.Buffer(Table.Column(AddIndex,col_name)),
           RunningTotal = Table.AddColumn(AddIndex, "Running Total", each List.Sum(List.FirstN(List,[Indeks]))),
           RemCol = Table.RemoveColumns(RunningTotal,{"Indeks"})
        in
           RemCol,
    // function based on List.Generate
        fxRunningTotalLG = (tab as table, col_name as text) =>
          
          let
             LTS = List.Buffer(Table.Column(tab, col_name)),
             ListCount = List.Count(LTS),
             LstGen = List.Generate(()=>
                    [Counter=1, RT= LTS{0}],
               each [Counter]<=ListCount,
               each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
               each [RT]),
             LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total"}}),
             TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
          in
             TblToCol,
    
    // choose column to grouping and to sum
        CFT = "Lot Id", // column name for grouping
        CFRT = "Final Time", // column name to sum
    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GrupCol = Table.Group(Source, {CFT}, {{"tab", each _, type table}}),
        RemCol = Table.SelectColumns(GrupCol,{"tab"}),
    // You can choose function fxRunningTotalLG or fxRunningTotal - arguments are the same
        TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),
        ExpCol = Table.ExpandTableColumn(TransfCol, "tab", {"Lot Id", "Final Time", "Running Total"}, {"Lot Id", "Final Time", "Running Total"})
    in
        ExpCol
    
    Regards :-)

    Tuesday, June 9, 2015 2:01 PM
  • Bill, you're genius!

    How smart to split up the Final Time values & feed them as a table into the function - no need to filter anything then!

    (... & thanks for setting up the racetrack :-))


    Imke

    Tuesday, June 9, 2015 3:45 PM
    Moderator
  • Thanks Bill , as mentioned By Imke , now I doesnt need to apply any filters

    Wednesday, June 10, 2015 10:32 AM
  • Thanks Imke for your kind words but i'm an ordinary guy only :-))

    I think, List.Generate method is better than List.First method in most cases.
    Especially if we have many thousand rows of data and a few subcategories only.
    In this case, efficiency is completely different.

    sq :-)

    Bill

    Thursday, June 11, 2015 2:24 PM
  • You are welcome :-)
    Thursday, June 11, 2015 2:25 PM
  • Hello Imke,now that we have the running total, we could perform an ABC analysis for each product. I tried to do that, but I got stuck firguring out how to calculate the grandTotal for each product type. I can get the grand total for the full list of product, but I am not able to get the grand total relative to each product. Can you please help?

    Sunday, July 5, 2015 9:57 AM
  • Hi Diego,

    Did you try Bill's query expression above? It should work for you. You just need to replace "Lot Id" with your Product type column name.

    If it doesn't work, please share the table schema that you have, and we can edit the expression above to work on your table.

    Thank you,

    Gil

    Sunday, July 5, 2015 10:02 AM
  • Hi Diego,

    is my understanding right, that you want to apply the technique to a table where you not only have the product but also an additional grouping Level/column for the products within whom you want to perform the running totals (product type)?

    Then you just need to pass the product type as a third field into the function and use it there to filter the source table (tab). Please tell if you need the code for that.


    Imke

    Sunday, July 5, 2015 12:55 PM
    Moderator
  • Well, and the GrandTotal for each product type would be a group on product type only with SUM on CFRT (you could self-join that on ProductGroup in your ABC-Analysis).

    Imke


    Sunday, July 5, 2015 1:04 PM
    Moderator
  • Hi Diego,

    here comes the code based on this example, where you have to aggregate on product first, because we have multiple lines per product.

    let
        fxRunningTotalLG = (tab as table, col_name as text, ProdGroup) =>
         
          let
             LTS = List.Buffer(Table.Column(Table.SelectRows(tab, each [ProductGroup] = ProdGroup), col_name)),
             ListCount = List.Count(LTS),
             LstGen = List.Generate(()=>
                    [Counter=1, RT= LTS{0}],
               each [Counter]<=ListCount,
               each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
               each [RT]),
             LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total"}}),
             TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
          in
             TblToCol,

    // choose column to grouping and to sum
        CFT = "Product", // column name for grouping
        CFRT = "Sale", // column name to sum

        Source = Excel.CurrentWorkbook(){[Name="SalesProductGroup"]}[Content],
        SubtotalProductGroup = Table.Group(Source, {"ProductGroup"}, {{"TotalProductGroup", each List.Sum([Sale]), type number}}),
        GroupedRows = Table.Group(Source, {"Product", "ProductGroup"}, {{"Sale", each List.Sum([Sale]), type number}}),
        SortedRows = Table.Sort(GroupedRows,{{"ProductGroup", Order.Ascending}, {"Sale", Order.Descending}}),
        Reorder = Table.ReorderColumns(SortedRows,{"Sale", "ProductGroup", "Product"}),
        Group = Table.Buffer(Table.Group(Reorder, {"ProductGroup"}, {{"tab", each _, type table}})),
        TransfCol = Table.AddColumn(Group, "Result", each fxRunningTotalLG(_[tab], CFRT, [ProductGroup])),
        RemOther = Table.SelectColumns(TransfCol,{"Result"}),
        ExpResult = Table.ExpandTableColumn(RemOther, "Result", {"Product", "Sale", "ProductGroup", "Running Total"}, {"Product", "Sale", "ProductGroup", "Running Total"}),
        MergeSubtotals = Table.NestedJoin(ExpResult,{"ProductGroup"},SubtotalProductGroup,{"ProductGroup"},"NewColumn"),
        ExpandSubtotals = Table.ExpandTableColumn(MergeSubtotals, "NewColumn", {"TotalProductGroup"}, {"TotalProductGroup"}),
        Percentage = Table.AddColumn(ExpandSubtotals, "Percentage", each [Running Total]/[TotalProductGroup]),
        Classification = Table.AddColumn(Percentage, "Classification", each if [Percentage] < 0.7 then "A" else if [Percentage] < 0.9 then "B" else "C")
    in
        Classification


    Imke

    Sunday, July 5, 2015 8:35 PM
    Moderator
  • Ouch, I've spoiled Bill's clever solution - here comes the tuned code - at the end it's like Gil said: Your Product group will be the new Lot ID (just that we need some aggregation and sorting before as well :-):

    let
        fxRunningTotalLG = (tab as table, col_name as text) =>
         
          let
             LTS = List.Buffer(Table.Column(tab, col_name)),
             ListCount = List.Count(LTS),
             LstGen = List.Generate(()=>
                    [Counter=1, RT= LTS{0}],
               each [Counter]<=ListCount,
               each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
               each [RT]),
             LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total"}}),
             TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
          in
             TblToCol,

    // choose column to grouping and to sum
        Prod = "Product", // column name for Product
    //    Amount = "Sale", // couldn't make it work on the Grouping here
        Group = "ProductGroup", // column name for Grouping

        Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="SalesProductGroup"]}[Content]),
    // you need to specify the name of your column to SUM here directly ([Sale])
        TotalsOnProduct = Table.Group(Source, {Prod, Group}, {{"Amount", each List.Sum([Sale]), type number}}),
        SortForABC = Table.Sort(TotalsOnProduct,{{Group, Order.Ascending}, {"Amount", Order.Descending}}),
        ReorderForABC = Table.ReorderColumns(SortForABC,{"Amount", Group, Prod}),
        GroupOnProductGroup = Table.Buffer(Table.Group(ReorderForABC, {Group}, {{"tab", each _, type table}})),
        RemoveField = Table.RemoveColumns(GroupOnProductGroup,{Group}),
        InvokeFunction = Table.TransformColumns(RemoveField, {"tab", each fxRunningTotalLG(_, "Amount")}),
        ShowResults = Table.ExpandTableColumn(InvokeFunction, "tab", {"Amount", Group, Prod, "Running Total"}, {"Amount", Group, Prod, "Running Total"}),
        CreateSubtotalOnProductGroup = Table.Group(TotalsOnProduct, {Group}, {{"TotalProductGroup", each List.Sum([Amount]), type number}}),
        SelfjoinSubtotals = Table.NestedJoin(ShowResults,{Group},CreateSubtotalOnProductGroup,{Group},"NewColumn"),
        ExpandSubtotals = Table.ExpandTableColumn(SelfjoinSubtotals, "NewColumn", {"TotalProductGroup"}, {"TotalProductGroup"}),
        Percentage = Table.AddColumn(ExpandSubtotals, "Percentage", each [Running Total]/[TotalProductGroup]),
        Classification = Table.AddColumn(Percentage, "Classification", each if [Percentage] < 0.7 then "A" else if [Percentage] < 0.9 then "B" else "C")
    in
        Classification


    Imke


    Monday, July 6, 2015 6:11 AM
    Moderator
  • Over 3 years later, I found a way to boost performance for this task even further: Basically taking Bills function and instead of applying it to every row of the table (like it will be done in his step :

        TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),

    my new solution will execute the List.Generate-function only once for each table:

    https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

    That makes a real difference, so if you after performance here, you should use that new function.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, January 2, 2019 8:29 AM
    Moderator