Forum Discussion

SiGill1979's avatar
SiGill1979
Frequent Visitor
6 years ago
Solved

Sum If

How can I sum a column if the value is less than a certain value and matches another value.   My Data is like this A B C Opening Stock 59 12508.92 201925   59 -2843.1 201926 1250...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Ooops copied the wrong query AND I did not put it in code block.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", 
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
    #"Expanded AllRows"
amitchandak's avatar
amitchandak
Super User
6 years ago

You need to have date/calendar dimension.

sales_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
  • SiGill1979's avatar
    SiGill1979
    Frequent Visitor
    6 years ago

    Am I able to use that in Power Query?

    I have found List.Accumulate but i can't get it to work?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The code below presumes that your data is in a table named "Table2". The process is to add an index column to the table that starts at 0. Then take the First chacters up to the index amount from the prior table B column and sum them up.

       

      This technique may be slow if you have a large table. In that case, we would add a List.Buffer as a separate step on the #"Added Index" table.

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Opening Stock.1", each List.Sum(List.FirstN(#"Added Index"[B],[Index])))
in
    #"Added Custom"

      Regards,

      Mike

      • SiGill1979's avatar
        SiGill1979
        Frequent Visitor
        6 years ago

        Thanks Mike

         

        That is very close to what I am looking for, thank you, does take a while to run though.

         

        But it needs to look at column C and only sum anything less than the current value in column C

        And also needs to look at column A and only sum if the current value matches column A. Hope that makes sense.

        Hence the =SUMIFS(B:B,C:C,"<"&C3,A:A,A3) is it was in Excel