Home » Sum Every Nth Occurrence based on a Criteria Challenge

Sum Every Nth Occurrence based on a Criteria Challenge

Sum Every Nth Occurrence per payment if the project meets the criteria Strictly Legacy Array Functions or PowerQuery Note: Every payment has 5 occurrences. Check if the payment occurrence criteria and project criteria per payment are met before summing data

📌 Challenge Details and Links
Challenge Number: 4
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sum Every Nth Occurrence based on a Criteria Challenge with Power Query

Power Query solution 1 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tblProjects"]}[Content], 
  GroupPay = Table.Combine(
    Table.Group(Source, {"Payment"}, {{"All", each Table.AddIndexColumn(_, "Occurrence", 1)}})[All]
  ), 
  Sol = Table.Sort(
    Table.Group(
      GroupPay, 
      {"Project"}, 
      {
        {
          "Total", 
          (x) =>
            let
              a = x, 
              b = Table.SelectRows(
                Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
                each [Criteria Project] = x[Project]{0}
              )[Occurrence]{0}, 
              c = List.Sum(Table.SelectRows(a, each [Occurrence] = b)[Amount])
            in
              c
        }
      }
    ), 
    "Project"
  )
in
  Sol
Power Query solution 2 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tblProjects"]}[Content], 
  G_1 = Table.Combine(
    Table.Group(
      Source, 
      {"Payment"}, 
      {{"All", each Table.AddIndexColumn([[Project], [Amount]], "Occurrence", 1)}}
    )[All]
  ), 
  G_2 = Table.Group(
    G_1, 
    {"Project", "Occurrence"}, 
    {{"Total", each List.Sum([Amount]), type number}}
  ), 
  LookupVal = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCijKz0pNLlFwVNJRMlKK1UGIOAFFjFFEnIEiJigiLkARU6XYWAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    {"Project", "Occurrence"}
  ), 
  CT = Table.TransformColumnTypes(LookupVal, {{"Occurrence", Int64.Type}}), 
  Output = Table.AddColumn(
    CT, 
    "Total", 
    each G_2{[Project = [Project], Occurrence = [Occurrence]]}?[Total]? ?? 0
  )
in
  Output
Power Query solution 3 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by Nelson Mwangi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tblProjects"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Payment"}, 
    {{"GroupedTable", each Table.AddIndexColumn(_, "Payment Occurence", 1, 1), type table}}
  ), 
  XpandTable = Table.ExpandTableColumn(
    Group, 
    "GroupedTable", 
    {"Project", "Amount", "Payment Occurence"}, 
    {"Project", "Amount", "Payment Occurence"}
  ), 
  CriteriaTable = {"Project A, 2", "Project B, 3", "Project C,4", "Project D,5"}, 
  Convrt = Table.FromList(CriteriaTable, Splitter.SplitTextByDelimiter(","), {"Proj", "Occurence"}), 
  DataType = Table.TransformColumnTypes(Convrt, {{"Occurence", Int64.Type}}), 
  JoinTables = Table.Join(XpandTable, "Project", DataType, "Proj", JoinKind.LeftOuter), 
  CustomCol = Table.AddColumn(JoinTables, "Custom", each [Payment Occurence] = [Occurence]), 
  FilterTrue = Table.SelectRows(CustomCol, each ([Custom] = true)), 
  GroupSol = Table.Group(
    FilterTrue, 
    {"Project", "Payment Occurence"}, 
    {{"Total", each List.Sum([Amount]), type number}}
  )
in
  GroupSol

Solving the challenge of Sum Every Nth Occurrence based on a Criteria Challenge with Excel

Excel solution 1 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by Pieter de B.: 
=SUMPRODUCT($E$3:$E$23*(MMULT((TRANSPOSE(
   $C$3:$C$23)=$C$3:$C$23)*(TRANSPOSE(
   ROW(
   $C$3:$C$23))<=ROW(
   $C$3:$C$23)),
   ROW(
   $C$3:$C$23)^0)=H3)*($B$3:$B$23=G3))
Excel solution 2 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by Ankur Sharma: 
=LET(a,
    tblProjects3[Project],
   
b,
    MOD(
        SEQUENCE(
            COUNTA(
                a)),
         5),
   
MAP(H3:H6,
    I3:I6,
   
LAMBDA(c,
   d,
    SUM(FILTER(tblProjects3[Amount],
    (a = c) * (IF(
        b = 0,
         5,
         b) = d),
    0)))))
Excel solution 3 for Sum Every Nth Occurrence based on a Criteria Challenge, proposed by JvdV –: 
=SUMPRODUCT((B$3:B$23=H3)*(MOD(
   ROW(
       B$3:B$23)+2,
   5)+1=I3),
   E$3:E$23)

Leave a Reply