Find Customer Stats With Conditions

Generate result table as shown.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 44
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Find Customer Stats With Conditions with Power Query

Power Query solution 1 for Find Customer Stats With Conditions, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.UnpivotOtherColumns(A, {"Item"}, "At", "V"), 
  C = Table.TransformColumns(B, {"At", each Text.Start(_, 4) & " Total"}), 
  D = Table.Pivot(C, List.Distinct(C[At]), "At", "V", List.Sum), 
  E = D & Table.PromoteHeaders(Table.Transpose(Table.Group(C, {"At"}, {"Sum", each List.Sum([V])}))), 
  F = Table.TransformColumns(E, {"Item", each if _ = null then "Total" else _}), 
  G = Table.AddColumn(F, "Till Date Total", each List.Sum(List.Skip(Record.ToList(_))))
in
  G

Power Query solution 2 for Find Customer Stats With Conditions, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Item"}, "Year", "Value"), 
  YearTransformed = Table.TransformColumns(
    Unpivot, 
    {{"Year", each Text.Start(_, 4) & " Total", type text}}
  ), 
  Grouped = Table.Group(
    YearTransformed, 
    {"Item", "Year"}, 
    {{"Total", each List.Sum([Value]), type number}}
  ), 
  Pivot = Table.Pivot(Grouped, List.Distinct(Grouped[Year]), "Year", "Total", List.Sum), 
  TotalColumn = Table.AddColumn(
    Pivot, 
    "Total", 
    each List.Sum(List.Skip(Record.ToList(_), 1)), 
    type number
  ), 
  TotalRow = Table.FromRows(
    {{"Total"} & List.Transform(List.Skip(Table.ToColumns(TotalColumn), 1), List.Sum)}, 
    Table.ColumnNames(TotalColumn)
  ), 
  Output = TotalColumn & TotalRow
in
  Output

Power Query solution 3 for Find Customer Stats With Conditions, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivoted = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), 
  Extracted = Table.TransformColumns(
    Unpivoted, 
    {{"Attribute", each Text.Start(_, 4) & " Total", type text}}
  ), 
  Grouped = Table.Group(
    Extracted, 
    {"Item", "Attribute"}, 
    {{"Count", each List.Sum([Value]), type number}}
  ), 
  Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[Attribute]), "Attribute", "Count"), 
  TotalRows = Table.AddColumn(
    Pivoted, 
    "Till Date Total", 
    each List.Sum(List.Skip(Record.ToList(_)))
  ), 
  SumaCol = {"Total"} & List.Transform(List.Skip(Table.ToColumns(TotalRows)), each List.Sum(_)), 
  Solucion = Table.InsertRows(
    TotalRows, 
    Table.RowCount(TotalRows), 
    {Record.FromList(SumaCol, Table.ColumnNames(TotalRows))}
  )
in
  Solucion

Power Query solution 4 for Find Customer Stats With Conditions, proposed by Luan Rodrigues:

Record.FieldValues()
Record.SelectFields()
Record.ToList()
Record.FromList()
Record.RemoveFields()

Power Query solution 5 for Find Customer Stats With Conditions, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  col = Table.UnpivotOtherColumns(Fonte, {"Item"}, "Atributo", "Valor"), 
  ano = Table.AddColumn(col, "Personalizar", each Text.Start([Atributo], 4) & " Total"), 
  grp = Table.Group(ano, {"Personalizar", "Item"}, {{"Contagem", each List.Sum(_[Valor])}}), 
  piv = Table.Pivot(grp, List.Distinct(grp[Personalizar]), "Personalizar", "Contagem"), 
  res = Table.AddColumn(
    piv, 
    "Till Date Total", 
    each List.Sum(List.RemoveFirstN(Record.FieldValues(_), 1))
  ), 
  Result = res
    & Table.FromRows(
      {List.Transform(Table.ToColumns(res), each try List.Sum(_) otherwise "Total")}, 
      Table.ColumnNames(res)
    )
in
  Result

Power Query solution 6 for Find Customer Stats With Conditions, proposed by Brian Julius:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 UnpivotOther = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
 SplitToYear = Table.SplitColumn(UnpivotOther, "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1", "Attribute.2"}),
 Group = Table.Group(SplitToYear, {"Item", "Attribute.1"}, {{"Total", each List.Sum([Value]), type number}}),
 Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute.1]), "Attribute.1", "Total"),
 TotalCol = Table.AddColumn(Pivot, "Addition", each [2021] + [2022], type number),
 Rename = Table.RenameColumns(TotalCol,{{"Addition", "Till Date Total"}, {"2022", "2022 Total"}, {"2021", "2021 Total"}}),
 T2021 = List.Sum( Rename[#"2021 Total"]),
 T2022 = List.Sum( Rename[#"2022 Total"]),
 TTotal = T2021 + T2022,
 TotalRow = 
 hashtag#table(
 { "Item", "2021 Total", "2022 Total", "Till Date Total" }, 
 { { "Total", T2021, T2022, TTotal } }
 ),
 Final = Table.Combine( { Rename, TotalRow})
in
 Final

//PS - the visual today reminds me of the video for Subterranean Homesick Blues...

Power Query solution 7 for Find Customer Stats With Conditions, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), 
  Replaced = Table.ReplaceValue(
    Unpivot, 
    null, 
    null, 
    (a, b, c) => Date.ToText(Date.FromText(a, [Format = "yyyyMM"]), "yyyy") & " Total", 
    {"Attribute"}
  ), 
  Pivot = Table.Pivot(Replaced, List.Distinct(Replaced[Attribute]), "Attribute", "Value", List.Sum), 
  Custom = Table.AddColumn(Pivot, "Till Date Total", each List.Sum(List.Skip(Record.ToList(_)))), 
  ExpectedOutput = Custom
    & Table.FromRows(
      {List.Transform(Table.ToColumns(Custom), each try List.Sum(_) otherwise "Total")}, 
      Table.ColumnNames(Custom)
    )
in
  ExpectedOutput

Power Query solution 8 for Find Customer Stats With Conditions, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  CH = Table.TransformColumnTypes(
    Source, 
    {
      {"Item", type text}, 
      {"202110", Int64.Type}, 
      {"202111", Int64.Type}, 
      {"202112", Int64.Type}, 
      {"202201", Int64.Type}, 
      {"202202", Int64.Type}, 
      {"202203", Int64.Type}
    }
  ), 
  Unp = Table.UnpivotOtherColumns(CH, {"Item"}, "Attribute", "Value"), 
  AdI = Table.AddColumn(Unp, "Year", each Text.Start([Attribute], 4) & " Total"), 
  ROC = Table.SelectColumns(AdI, {"Item", "Year", "Value"}), 
  GR = Table.Group(ROC, {"Item", "Year"}, {{"TotalCount", each List.Sum([Value]), type number}}), 
  PI = Table.Pivot(GR, List.Distinct(GR[Year]), "Year", "TotalCount", List.Sum), 
  IN = Table.AddColumn(PI, "Addition", each [2021 Total] + [2022 Total], type number), 
  Tbl1 = Table.RenameColumns(IN, {{"Addition", "Till Date Total"}}), 
  AddC = Table.AddColumn(Tbl1, "Total", each "Total"), 
  Tbl2 = Table.Group(
    AddC, 
    {"Total"}, 
    {
      {"2021 Total", each List.Sum([2021 Total]), type nullable number}, 
      {"2022 Total", each List.Sum([2022 Total]), type nullable number}, 
      {"Till Date Total", each List.Sum([Till Date Total]), type number}
    }
  ), 
  RCN = Table.RenameColumns(Tbl2, {{"Total", "Item"}}), 
  C = Table.Combine({Tbl1, RCN})
in
  C

Power Query solution 9 for Find Customer Stats With Conditions, proposed by Matthias Friedmann:

let
 Source = Excel.CurrentWorkbook(){[Name="YearTotal"]}[Content],
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
 #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, 4), type text}}),
 #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Attribute]), "Attribute", "Value", List.Sum),
 Sum = Table.AddColumn(#"Pivoted Column", "Till Date", each List.Sum(
 Record.ToList(
 Record.SelectFields(_, 
 List.Skip(Table.ColumnNames(#"Pivoted Column"))
 ) ))),
 Custom1 = Table.FromColumns(
 List.Transform(Table.ToColumns(Sum), each _ & {try List.Sum(_) otherwise "Total"}),
 Table.ColumnNames(Sum))
in
 Custom1


The error was wrapped in {} => try... otherwise couldn't catch it:
List.Transform(Table.ToColumns(Sum), each _ & (try {List.Sum(_)} otherwise {"Total"})) ❌
Instead wrap {} around try..otherwise
List.Transform(Table.ToColumns(Sum), each _ & {try List.Sum(_) otherwise "Total"}) ✔

Power Query solution 10 for Find Customer Stats With Conditions, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Years = List.Distinct(List.Transform(List.Skip(Table.ColumnNames(Source)), each Text.Start(_, 4))), 
  Group = Table.Group(
    Source, 
    {"Item"}, 
    {
      {
        "all", 
        (a) =>
          let
            l = List.Transform(
              Years, 
              (b) =>
                List.Sum(
                  List.Combine(
                    Table.ToColumns(
                      Table.SelectColumns(
                        a, 
                        List.Select(Table.ColumnNames(Source), (c) => Text.StartsWith(c, b))
                      )
                    )
                  )
                )
            )
          in
            Record.FromList(
              {a[Item]{0}} & l & {List.Sum(l)}, 
              {"Item"} & List.Transform(Years, each _ & " Total") & {"Till Date Total"}
            )
      }
    }
  ), 
  FromRecs = Table.FromRecords(Group[all]), 
  Total = FromRecs
    & Table.FromColumns(
      List.Transform(Table.ToColumns(FromRecs), each {try List.Sum(_) otherwise "Total"}), 
      Table.ColumnNames(FromRecs)
    )
in
  Total

Power Query solution 11 for Find Customer Stats With Conditions, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  UnpivotOthCols = Table.UnpivotOtherColumns(Source, {"Item"}, "Atrr", "Value"), 
  TransCol = Table.TransformColumns(UnpivotOthCols, {"Atrr", each Text.Start(_, 4) & " Total"}), 
  PivotCol = Table.Pivot(TransCol, List.Distinct(TransCol[Atrr]), "Atrr", "Value", List.Sum), 
  TotalSum = Table.FromRows(
    {{"Total"} & List.Transform(List.Skip(Table.ToColumns(PivotCol)), each List.Sum(_))}, 
    Table.ColumnNames(PivotCol)
  ), 
  Result = Table.AddColumn(
    PivotCol & TotalSum, 
    "Till Date Total", 
    each List.Sum(List.Skip(Record.ToList(_)))
  )
in
  Result

Power Query solution 12 for Find Customer Stats With Conditions, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), 
  #"Extracted First Characters" = Table.TransformColumns(
    #"Unpivoted Other Columns", 
    {{"Attribute", each Text.Start(_, 4), type text}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Extracted First Characters", 
    List.Distinct(#"Extracted First Characters"[Attribute]), 
    "Attribute", 
    "Value", 
    List.Sum
  ), 
  #"Inserted Addition" = Table.AddColumn(
    #"Pivoted Column", 
    "Total", 
    each [2022] + [2021], 
    type number
  ), 
  Custom1 = {"Total"} & List.Transform(List.Skip(Table.ToColumns(#"Inserted Addition")), List.Sum), 
  Custom2 = Table.FromRows(
    Table.ToRows(#"Inserted Addition") & {Custom1}, 
    Table.ColumnNames(#"Inserted Addition")
  )
in
  Custom2

Power Query solution 13 for Find Customer Stats With Conditions, proposed by YOGESH KUMAR:

let
 Source = Excel.Workbook(File.Contents("C:UsersYoshriOneDriveDesktopTask1.xlsx"), null, true),
 Sheet1_Sheet = Table.PromoteHeaders(Source{[Item="Sheet1",Kind="Sheet"]}[Data]),
 Unpivot = Table.UnpivotOtherColumns(Sheet1_Sheet,{"Item"},"Attribute","Value"),
 AddYearcol = Table.AddColumn(Unpivot,"Year",each Text.Start([Attribute],4)&" "&"Total"),
 Rmvcolmn = Table.RemoveColumns(AddYearcol,"Attribute"),
 #"Pivoted Column" = Table.Pivot(Rmvcolmn, List.Distinct(Rmvcolmn[Year]), "Year", "Value", List.Sum),
 Ttlclmn = Table.AddColumn(#"Pivoted Column","Till Date Total",each [2021 Total]+[2022 Total]),
 ClmnName= Table.Transpose(Table.FromList(Table.ColumnNames(Ttlclmn))),
 Val_1= List.Sum(Ttlclmn[2021 Total]),
 Val_2= List.Sum(Ttlclmn[2022 Total]),
 Val_3= List.Sum(Ttlclmn[Till Date Total]),
 Val_4="Total",
 Appended_Value= List.Combine({{Val_4},{Val_1},{Val_2},{Val_3}}),
 #"Converted to Table" = Table.Transpose(Table.FromList(Appended_Value, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)),
 Custom2 = Table.PromoteHeaders(Table.Combine({ClmnName,#"Converted to Table"})),
 EndOutput = Table.Combine({Ttlclmn, Custom2})
in
 EndOutput

Power Query solution 14 for Find Customer Stats With Conditions, proposed by Herman Lee:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
 #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Year", "Month"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Year", type number}, {"Month", type number}, {"Value", type number}})
in
 #"Changed Type"

Power Pivot:
Columns: Year
Rows: Item
Values: Sum of Value

Power Query solution 15 for Find Customer Stats With Conditions, proposed by Alejandra Horvath CPA, CGA:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivoted = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), 
  ExtractedYr = Table.TransformColumns(
    Unpivoted, 
    {{"Attribute", each Text.Start(_, 4) & " Total", type text}}
  ), 
  AddItems = Table.Group(
    ExtractedYr, 
    {"Item", "Attribute"}, 
    {{"Count", each List.Sum([Value]), type nullable number}}
  ), 
  GroupForYrTotals = Table.Group(
    AddItems, 
    {"Attribute"}, 
    {{"Count", each List.Sum([Count]), type nullable number}}
  ), 
  NewTable = Table.PromoteHeaders(
    Table.Transpose(Table.InsertRows(GroupForYrTotals, 2, {[Attribute = "Item", Count = "Total"]}))
  ), 
  Pivoted = Table.Pivot(
    AddItems, 
    List.Distinct(AddItems[Attribute]), 
    "Attribute", 
    "Count", 
    List.Sum
  )
    & NewTable, 
  TillDateTotal = Table.AddColumn(
    Pivoted, 
    "Till Date Total", 
    each List.Sum(Record.ToList(Record.RemoveFields(_, "Item")))
  )
in
  TillDateTotal

Power Query solution 16 for Find Customer Stats With Conditions, proposed by Alejandra Horvath CPA, CGA:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Unpivoted = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
 ExtractedYr = Table.TransformColumns(Unpivoted, {{"Attribute", each Text.Start(_, 4) & " Total", type text}}),
 AddItems = Table.Group(ExtractedYr, {"Item", "Attribute"}, {{"Count", each List.Sum([Value]), type nullable number}}),
 Pivoted = Table.Pivot(AddItems, List.Distinct(AddItems[Attribute]), "Attribute", "Count", List.Sum),
 AddTotals = Pivoted & hashtag#table ({"Item", "2021 Total", "2022 Total"}, {{"Total", List.Sum(Pivoted[2021 Total]), List.Sum(Pivoted[2022 Total])}}),
 TillTotal = Table.AddColumn(AddTotals, "Till Date Total", each [2022 Total] + [2021 Total], type number)
in
 TillTotal

Solving the challenge of Find Customer Stats With Conditions with Excel

Excel solution 1 for Find Customer Stats With Conditions, proposed by Bo Rydobon 🇹🇭:

=LET(
    t,
    "Total",
    s,
    "Till Date",
    a,
    A2:A9,
    b,
    VSTACK(
        A1,
        UNIQUE(
            a
        ),
        t
    ),
    y,
    LEFT(
        B1:G1,
        4
    ),
    z,
    HSTACK(
        UNIQUE(
            y,
            1
        ),
        s
    ),
    d,
    B2:G9,
    
    HSTACK(
        b,
        MAP(
            IFNA(
                b,
                z
            ),
            IFNA(
                z,
                b
            ),
            LAMBDA(
                i,
                w,
                IF(
                    i=A1,
                    w&" "&t,
                    SUM(
                        d*IF(
                            i=t,
                            1,
                            i=a
                        )*IF(
                            w=s,
                            1,
                            w=y
                        )
                    )
                )
            )
        )
    )
)

Excel solution 2 for Find Customer Stats With Conditions, proposed by محمد حلمي:

=LET(
a,
    A2:A9,
    
b,
    B1:G1,
    
u,
    UNIQUE(
        a
    ),
    
v,
    UNIQUE(
        LEFT(
            b,
            4
        ),
        1
    ),
    
s,
    MAKEARRAY(ROWS(
        u
    ),
    COUNTA(
        v
    ),
    LAMBDA(r,
    c,
    
SUM(IF((
INDEX(
    u,
    r
)=a)*(INDEX(
    v,
    c
)=LEFT(
            b,
            4
        )),
    B2:G9)))),
    
r,
    BYROW(
        s,
        LAMBDA(
            r,
            SUM(
                r
            )
        )
    ),
    
c,
    BYCOL(
        s,
        LAMBDA(
            r,
            SUM(
                r
            )
        )
    ),
    
VSTACK(
    
    HSTACK(
        A1,
        v&" Total",
        "Till Date Total"
    ),
    
    HSTACK(
        u,
        s,
        r
    ),
    
    HSTACK(
        "Total",
        c,
        SUM(
            c
        )
    )
))

Excel solution 3 for Find Customer Stats With Conditions, proposed by 🇰🇷 Taeyong Shin:

=LET(
    h,
    --LEFT(
        B1:G1,
        4
    ),
    c,
    A2:A9,
    u,
    UNIQUE(
        c
    ),
    hu,
    UNIQUE(
        h,
        1
    ),
    m,
    REDUCE(
        u,
        hu,
        LAMBDA(
            a,
            v,
            HSTACK(
                a,
                MMULT(
                    N(
                        u=TOROW(
        c
    )
                    ),
                    MMULT(
                        --B2:G9,
                        TOCOL(
                            DELTA(
                                h,
                                v
                            )
                        )
                    )
                )
            )
        )
    ),
    t,
    VSTACK(
        HSTACK(
            "Item",
            hu&"Total",
            0
        ),
        REDUCE(
            m,
            {1,
            1},
            LAMBDA(
                a,
                _,
                TRANSPOSE(
                    HSTACK(
                        a,
                        BYROW(
                            a,
                            SUM
                        )
                    )
                )
            )
        )
    ),
    IF(
        t=0,
        "Total",
        t
    )
)


=LET(
    F,
    LAMBDA(
        x,
        TOCOL(
            IFS(
                B2:G9,
                x
            ),
            2
        )
    ),
    p,
    PIVOTBY(
        F(
            A2:A9
        ),
        F(
            LEFT(
        B1:G1,
        4
    )
        ),
        F(
            B2:G9
        ),
        SUM
    ),
    IF(
        SEQUENCE(
            ROWS(
                p
            ),
            COLUMNS(
                p
            )
        )=1,
        "Item",
        p
    )
)

Excel solution 4 for Find Customer Stats With Conditions, proposed by 🇰🇷 Taeyong Shin:

=LET(
 Col,
     A2:A9,
    
 Uitem,
     UNIQUE(
         Col
     ),
    
 Y,
     LEFT(
         B1:G1,
          4
     ),
    
 Uyear,
     UNIQUE(
         Y,
          1
     ),
    
 rn,
     ROWS(
         Uitem
     ) + 1,
    
 cn,
     COLUMNS(
         Uyear
     ) + 1,
    
 Body,
     MAKEARRAY( rn,
     cn,
     LAMBDA(r,
    c,
    
 SUM(IF(r < rn,
     (Col = @INDEX(
         Uitem,
          r
     )),
     1) * IF(c < cn,
     (Y = INDEX(
         Uyear,
          c
     )),
     1) * B2:G9)
 )),
    
 HSTACK(
     VSTACK(
         A1,
          Uitem,
          "Total"
     ),
      VSTACK(
          HSTACK(
              Uyear & " Total",
               "Till Date Total"
          ),
           Body
      )
 )
)

Excel solution 5 for Find Customer Stats With Conditions, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d,
     A1:G9,
    
 _i,
     TAKE(
         DROP(
             _d,
              1
         ),
          ,
          1
     ),
    
 _h,
     LEFT(
         DROP(
             TAKE(
             _d,
              1
         ),
              ,
              1
         ),
          4
     ) & " Total",
    
 _b,
     DROP(
         _d,
          1,
          1
     ),
    
 _upi,
     TOCOL(
         IFNA(
             _i,
              _h
         )
     ),
    
 _upy,
     TOCOL(
         IFNA(
             _h,
              _i
         )
     ),
    
 _upb,
     TOCOL(
         _b
     ),
    
 _ui,
     UNIQUE(
         _i
     ),
    
 _uy,
     UNIQUE(
         _h,
          1
     ),
    
 _e1,
     LAMBDA(r,
     c,
    
 SUM(_upb * (_upi = INDEX(
     _ui,
      r
 )) * (_upy = INDEX(
     _uy,
      c
 )))
 ),
    
 _e2,
     LAMBDA(
         a,
          SUM(
              a
          )
     ),
    
 _c,
     MAKEARRAY(
         ROWS(
             _ui
         ),
          COLUMNS(
              _uy
          ),
          _e1
     ),
    
 _rt,
     BYROW(
         _c,
          _e2
     ),
    
 _ct,
     HSTACK(
         "Total",
          BYCOL(
         _c,
          _e2
     ),
          SUM(
         _b
     )
     ),
    
 _r,
     VSTACK(
         HSTACK(
             _ui,
              _c,
              _rt
         ),
          _ct
     ),
    
 _r
)

Excel solution 6 for Find Customer Stats With Conditions, proposed by Md. Zohurul Islam:

=LET(
    u,
    A2:A9,
    v,
    B1:G1,
    w,
    B2:G9,
    
    f,
    LAMBDA(
        x,
        y,
        TOCOL(
            IFNA(
                x,
                y
            )
        )
    ),
    
    a,
    PIVOTBY(
        f(
            u,
            v
        ),
        LEFT(
            f(
                v,
                u
            ),
            4
        )&" Total",
        TOCOL(
            w
        ),
        SUM,
        0,
        0,
        ,
        0
    ),
    
    b,
    IF(
        a="",
        "Item",
        a
    ),
    
    c,
    DROP(
        b,
        1,
        1
    ),
    
    d,
    HSTACK(
        c,
        BYROW(
            c,
            SUM
        )
    ),
    
    e,
    HSTACK(
        "Total",
         BYCOL(
             d,
             SUM
         )
    ),
    
    g,
    HSTACK(
        DROP(
            TAKE(
                b,
                1
            ),
            ,
            1
        ),
        "Till Date Total"
    ),
    
    h,
    VSTACK(
        HSTACK(
            TAKE(
                b,
                ,
                1
            ),
            VSTACK(
                g,
                d
            )
        ),
        e
    ),
    
    h
)

Excel solution 7 for Find Customer Stats With Conditions, proposed by Stefan Olsson:

=BYCOL(
    
    BYROW(
        
        QUERY(
            {A1:G9},
            
            "select Col1, sum(Col2)+sum(Col3)+sum(Col4), sum(Col5)+sum(Col6)+sum(Col7) Group by Col1 Label sum(Col2)+sum(Col3)+sum(Col4) '2021 Total', sum(Col5)+sum(Col6)+sum(Col7) '2022 Total'",
            1
        ),
        
        LAMBDA(
            rr,
            
            {rr,
            IF(
                rr="Item",
                 "Till Date Total",
                 sum(
                     rr
                 )
            )}
            
        )
    ),
     
    LAMBDA(
        cc,
         
        {cc; IF(
            cc="Item",
             "Total",
             sum(
                 cc
             )
        )}
        
    )
)

Solving the challenge of Find Customer Stats With Conditions with Python in Excel

Python in Excel solution 1 for Find Customer Stats With Conditions, proposed by Alejandro Campos:

df = xl("A1:G9", headers=True)
unique_items = df['Item'].dropna().unique()
year_columns = df.columns[1:]
years = [col[:4] for col in year_columns]
unique_years = sorted(set(years))
pivot_table = pd.DataFrame(index=unique_items + ['Total'], columns=[year + ' Total' for year in unique_years] + ['Till Date Total'])
for item in unique_items:
 for year in unique_years:
 filtered_df = df[df['Item'] == item]
 year_col = [col for col in year_columns if col.startswith(year)]
 if year_col:
 pivot_table.at[item, year + ' Total'] = filtered_df[year_col].sum().sum()
 else:
 pivot_table.at[item, year + ' Total'] = np.nan
 
 pivot_table.at[item, 'Till Date Total'] = filtered_df[year_columns].sum().sum()
pivot_table.loc['Total'] = pivot_table.sum()
pivot_table.drop(pivot_table.index[0:5], inplace=True)
pivot_table

Solving the challenge of Find Customer Stats With Conditions with SQL

SQL solution 1 for Find Customer Stats With Conditions, proposed by Zoran Milokanović:

SELECT /* Microsoft SQL Server 2019 */
 ISNULL(T.Item, 'Total') AS Item
,SUM(T.QUANTITY) AS "Till Date Total"
FROM
(
 SELECT
 CAST(D.Item AS VARCHAR(MAX)) AS Item
 ,D."202110", D."202111", D."202112", D."202201", D."202202", D."202203"
 FROM DATA D
) S
UNPIVOT
(
 QUANTITY FOR DATES IN ("202110", "202111", "202112", "202201", "202202", "202203")
) T
GROUP BY
 CUBE(T.Item)
;

Solving the challenge of Find Customer Stats With Conditions with Power Query

Solving the challenge of Find Customer Stats With Conditions with Excel

Solving the challenge of Find Customer Stats With Conditions with Python in Excel

Solving the challenge of Find Customer Stats With Conditions with SQL

Leave a Reply Cancel reply