none
Sum rows based on condition RRS feed

  • Question

  • I have a data set as below but "X" column is what I need .

    Country Product Category Week Month Week id Store Turnover Category Turnover  X
    DE A HC week1 JAN                     1 store1 2 21
    DE A HC week1 JAN                     1 store2 2 21
    DE A HC week1 JAN                     1 store3 3 21
    DE A HC week1 JAN                     1 store4 4 21
    DE A HC week2 JAN                     2 store2 6 17
    DE A HC week3 JAN                     3 store2 8 41
    DE A HC week3 JAN                     3 store3 9 41
    DE A HC week4 JAN                     4 store2 10 66 820
    DE A HC week4 JAN                     4 store3 11 66 820
    DE A HC week4 JAN                     4 store4 12 66 820
    DE A HC week5 FEB                     5 store2 10 36 702
    DE A HC week5 FEB                     5 store3 13 36 702
    DE A HC week6 FEB                     6 store1 14 90 1249
    DE A HC week6 FEB                     6 store3 15 90 1249
    DE A HC week6 FEB                     6 store4 16 90 1249
    DE A HC week7 FEB                     7 store2 17 34 1112
    DE A HC week8 FEB                     8 store2 18 74 1012
    DE A HC week8 FEB                     8 store3 19 74 1012
    DE B HC week1 JAN                     1 store1 1 21
    DE B HC week1 JAN                     1 store2 2 21
    DE B HC week1 JAN                     1 store3 3 21
    DE B HC week1 JAN                     1 store4 4 21
    DE B HC week2 JAN                     2 store1 5 17
    DE B HC week2 JAN                     2 store2 6 17
    DE B HC week3 JAN                     3 store1 7 41
    DE B HC week3 JAN                     3 store2 8 41
    DE B HC week3 JAN                     3 store3 9 41
    DE B HC week4 JAN                     4 store2 10 66 820
    DE B HC week4 JAN                     4 store3 11 66 820
    DE B HC week4 JAN                     4 store4 12 66 820
    DE B HC week5 FEB                     5 store2 13 36 760
    DE B HC week6 FEB                     6 store2 14 90 1249
    DE B HC week6 FEB                     6 store3 15 90 1249
    DE B HC week6 FEB                     6 store4 16 90 1249
    DE B HC week7 FEB                     7 store2 17 34 1022
    DE B HC week8 FEB                     8 store2 18 74 922
    DE B HC week8 FEB                     8 store3 19 74 922

    How X is should be calculated?

    - It sums up last 4 weeks (week id) for Category and Country (you can see only sample of data with one Category and one Country, in fact there are more)

    - Moreover, It should sum up only the stores in which my product was sold in past 4 weeks.

    Dont worry about blanks in X. It is because is not applicable when computing past 4 weeks.

    After all I will remove this rows.

    Complicated? I know. If it's not clear - I will explain everything




    • Edited by Gordonik Wednesday, July 11, 2018 10:41 PM
    Wednesday, July 11, 2018 10:34 PM

Answers

  • Hi there,

    I apologize for now getting back to you sooner. I got busy and lost track of your post. Please check if the following code provides what you are looking for.

    let
        Table.SplitByGroup = (table as table, groupColumns as list, returnColumn as text) =>
        let
            Grouped = Table.Group(table, groupColumns, {"List", each Table.Column(_, returnColumn)}),
            ColumnLists = Grouped[List]
        in
            ColumnLists,
    
        Source = ...,
        
        //Group table by Country and Category. Within the grouping step, add a column of tables
        //Each table in the added column is filtered from the main table based on 4-week windows
        
        GroupedRows = 
            Table.Group(
                Source, 
                {"Country", "Category"}, 
                {"Table", (i) => 
                     Table.AddColumn(
                         i, 
                         "X", 
                         (j) => 
                         Table.SelectRows(i, (k) => k[Week id] <= j[Week id] and k[Week id] >= j[Week id] - 3),
                         type table
                     ), 
                  type table
                 }
            ),
        CombinedTables = Table.Combine(GroupedRows[Table]),
        
        /*Each table in the column of tables must be filtered further to select only stores that overlap the products
        The Table.SplitByGroup function creates store lists for each product with the 4-week window
        List.Intersect is then used to get the stores that overlap
        List.Contains is then used to determine if the current store value is in the overlap list. If not, the row is filtered out*/
    
        FilteredTables = 
            Table.TransformColumns(
                CombinedTables, 
                {"X", 
                 (i) => Table.SelectRows(i, (j) => List.Contains(List.Intersect(Table.SplitByGroup(i, {"Product"}, "Store")), j[Store]))
                }
            ),
        
        //Each table in the column of tables is now filtered by all required criteria. We can now total the Category Turnover column.
        
        Totals = Table.TransformColumns(FilteredTables, {"X", each List.Sum(_[Category Turnover])}),
        ReplacedValues = Table.ReplaceValue(Totals, each [X], each if [Week id] < 4 then null else [X], Replacer.ReplaceValue, {"X"}),
        ChangedType = Table.TransformColumnTypes(ReplacedValues, {"X", type number})
    in
        ChangedType

    Monday, July 16, 2018 4:53 PM

All replies

  • What values are being summed to get the numbers that you show in the X column that you want to create (820, 702, 1249 etc.)?
    Thursday, July 12, 2018 5:32 AM
  • Hi Colin,

    I would like to explain more but can not paste any links and images and attachment

    (Body text cannot contain images or links until we are able to verify your account.)

    @microsoft, please verify my account

    For instance 702 is calculated based on sum of Category Turnover for

    rows 5-12, 24 and 26-32 (I have added index to refer to rows)

    Rows 5-12 are last 4 weeks for product A

    Rows 24-31 are last 4 weeks for product B  

    Rows 23 and 25 are excluded because its refers to Store1. Product A was not sold in Store1 in past 4 weeks.

    I mean weeks 2,3,4,5 in this example.


    My purpose is to have a column with sum of Category Turnover:

    - for past 4 weeks,

    - sum of all products from category and country

    - only for stores selling given product in past 4 weeks. 

    If it is still too complicated I will try to explain different way

    index Country Product Category Week Month Week id Store Turnover Category Turnover  X
    1 DE A HC week1 JAN                                      1 store1 2 21
    2 DE A HC week1 JAN                                      1 store2 2 21
    3 DE A HC week1 JAN                                      1 store3 3 21
    4 DE A HC week1 JAN                                      1 store4 4 21
    5 DE A HC week2 JAN                                      2 store2 6 17
    6 DE A HC week3 JAN                                      3 store2 8 41
    7 DE A HC week3 JAN                                      3 store3 9 41
    8 DE A HC week4 JAN                                      4 store2 10 66 820
    9 DE A HC week4 JAN                                      4 store3 11 66 820
    10 DE A HC week4 JAN                                      4 store4 12 66 820
    11 DE A HC week5 FEB                                      5 store2 10 36 702
    12 DE A HC week5 FEB                                      5 store3 13 36 702
    13 DE A HC week6 FEB                                      6 store1 14 90 1249
    14 DE A HC week6 FEB                                      6 store3 15 90 1249
    15 DE A HC week6 FEB                                      6 store4 16 90 1249
    16 DE A HC week7 FEB                                      7 store2 17 34 1112
    17 DE A HC week8 FEB                                      8 store2 18 74 1012
    18 DE A HC week8 FEB                                      8 store3 19 74 1012
    19 DE B HC week1 JAN                                      1 store1 1 21
    20 DE B HC week1 JAN                                      1 store2 2 21
    21 DE B HC week1 JAN                                      1 store3 3 21
    22 DE B HC week1 JAN                                      1 store4 4 21
    23 DE B HC week2 JAN                                      2 store1 5 17
    24 DE B HC week2 JAN                                      2 store2 6 17
    25 DE B HC week3 JAN                                      3 store1 7 41
    26 DE B HC week3 JAN                                      3 store2 8 41
    27 DE B HC week3 JAN                                      3 store3 9 41
    28 DE B HC week4 JAN                                      4 store2 10 66 820
    29 DE B HC week4 JAN                                      4 store3 11 66 820
    30 DE B HC week4 JAN                                      4 store4 12 66 820
    31 DE B HC week5 FEB                                      5 store2 13 36 760
    32 DE B HC week6 FEB                                      6 store2 14 90 1249
    33 DE B HC week6 FEB                                      6 store3 15 90 1249
    34 DE B HC week6 FEB                                      6 store4 16 90 1249
    35 DE B HC week7 FEB                                      7 store2 17 34 1022
    36 DE B HC week8 FEB                                      8 store2 18 74 922
    37 DE B HC week8 FEB                                      8 store3 19 74 922


    Thursday, July 12, 2018 9:48 AM
  • Hi there,

    I apologize for now getting back to you sooner. I got busy and lost track of your post. Please check if the following code provides what you are looking for.

    let
        Table.SplitByGroup = (table as table, groupColumns as list, returnColumn as text) =>
        let
            Grouped = Table.Group(table, groupColumns, {"List", each Table.Column(_, returnColumn)}),
            ColumnLists = Grouped[List]
        in
            ColumnLists,
    
        Source = ...,
        
        //Group table by Country and Category. Within the grouping step, add a column of tables
        //Each table in the added column is filtered from the main table based on 4-week windows
        
        GroupedRows = 
            Table.Group(
                Source, 
                {"Country", "Category"}, 
                {"Table", (i) => 
                     Table.AddColumn(
                         i, 
                         "X", 
                         (j) => 
                         Table.SelectRows(i, (k) => k[Week id] <= j[Week id] and k[Week id] >= j[Week id] - 3),
                         type table
                     ), 
                  type table
                 }
            ),
        CombinedTables = Table.Combine(GroupedRows[Table]),
        
        /*Each table in the column of tables must be filtered further to select only stores that overlap the products
        The Table.SplitByGroup function creates store lists for each product with the 4-week window
        List.Intersect is then used to get the stores that overlap
        List.Contains is then used to determine if the current store value is in the overlap list. If not, the row is filtered out*/
    
        FilteredTables = 
            Table.TransformColumns(
                CombinedTables, 
                {"X", 
                 (i) => Table.SelectRows(i, (j) => List.Contains(List.Intersect(Table.SplitByGroup(i, {"Product"}, "Store")), j[Store]))
                }
            ),
        
        //Each table in the column of tables is now filtered by all required criteria. We can now total the Category Turnover column.
        
        Totals = Table.TransformColumns(FilteredTables, {"X", each List.Sum(_[Category Turnover])}),
        ReplacedValues = Table.ReplaceValue(Totals, each [X], each if [Week id] < 4 then null else [X], Replacer.ReplaceValue, {"X"}),
        ChangedType = Table.TransformColumnTypes(ReplacedValues, {"X", type number})
    in
        ChangedType

    Monday, July 16, 2018 4:53 PM
  • Hi Colin,

    Let me apologize for late answer too :)

    I tested your code and I appreciate you find time to help me with this.

    Good example how the looping works and how to use custom function in M too.

    It works almost great. I think minor change is necessary. 

    In 3 cases you output (X) does not match my (X1) :

    Let's give another example how 1112 was calculated. (The value corresponding to 1022 in your output):

    Store 1 is included for calculations in case of product A but excluded for calculation for product B:

    Because B was not sold in store1 i last 4 weeks.

    I think the issue is in grouping where product is not included into the code (but I am not sure). Could you advice?

    • Edited by Gordonik Thursday, July 26, 2018 9:47 AM
    Thursday, July 26, 2018 9:32 AM
  • Gordonik, were you able to modify Colin's solution above in order to find a solution?

    Ehren

    Monday, December 3, 2018 7:01 PM
    Owner