Weekday Weekend Sales Report

Calculate the sum of sale for Weekdays and Weekends. Also list down the highest and lowest selling items on the basis of total sale.

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

Solving the challenge of Weekday Weekend Sales Report with Power Query

Power Query solution 1 for Weekday Weekend Sales Report, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Table.Group(
      Table.AddColumn(
        Source, 
        "Day Type", 
        each "Week" & (if Date.DayOfWeek([Date], 1) < 5 then "day" else "end")
      ), 
      {"Day Type", "Item"}, 
      {"Sum", each List.Sum([Sale])}
    ), 
    "Day Type", 
    {
      {"Total Sales", each List.Sum([Sum])}, 
      {
        "Highest Selling Item", 
        each Text.Combine(Table.SelectRows(_, (x) => x[Sum] = List.Max([Sum]))[Item], ", ")
      }, 
      {
        "Lowest Selling Item", 
        each Text.Combine(Table.SelectRows(_, (x) => x[Sum] = List.Min([Sum]))[Item], ", ")
      }
    }
  )
in
  Group

Power Query solution 2 for Weekday Weekend Sales Report, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  G = (_, f) =>
    let
      s = each [Sale], 
      t = Table.Group(_, {"Item"}, {{"Sale", each List.Sum(s(_))}})
    in
      Text.Combine(Table.SelectRows(t, each s(_) = f(s(t)))[Item], ", "), 
  S = Table.Group(
    Table.AddColumn(
      Source, 
      "Day Type", 
      each {"Weekend", "Weekday"}{Number.From(Date.DayOfWeek([Date], 1) < 5)}
    ), 
    {"Day Type"}, 
    {
      {"Total Sales", each List.Sum([Sale])}, 
      {"Highest Selling Item", each G(_, List.Max)}, 
      {"Lowest Selling Item", each G(_, List.Min)}
    }
  )
in
  S

Power Query solution 3 for Weekday Weekend Sales Report, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  MyFun = (Table, Function) =>
    let
      M = Function(Table[Sales]), 
      S = Table.SelectRows(Table, each [Sales] = M)[Item], 
      R = Text.Combine(S, ", ")
    in
      R, 
  Type = Table.AddColumn(
    Source, 
    "Day Type", 
    each if Date.DayOfWeek([Date], Day.Monday) < 5 then "Weekday" else "Weekend"
  ), 
  Group = Table.Group(Type, {"Day Type", "Item"}, {"Sales", each List.Sum([Sale])}), 
  Return = Table.Group(
    Group, 
    "Day Type", 
    {
      {"Total Sales", each List.Sum([Sales])}, 
      {"Highest Selling", each MyFun(_, List.Max)}, 
      {"Lowest Selling", each MyFun(_, List.Min)}
    }
  )
in
  Return

Power Query solution 4 for Weekday Weekend Sales Report, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "Day Type", 
    each if Date.DayOfWeek([Date]) = 0 or Date.DayOfWeek([Date]) = 6 then "Weekday" else "Weekend"
  ), 
  gp = Table.Group(
    add, 
    {"Day Type"}, 
    {
      {"Total Sales", each List.Sum(_[Sale])}, 
      {
        "Highest Selling Item", 
        each 
          let
            a = Table.Group(_, {"Item"}, {"soma", each List.Sum(_[Sale])}), 
            b = Table.SelectRows(a, each [soma] = List.Max(a[soma]))[Item]
          in
            Text.Combine(b, ", ")
      }, 
      {
        "Lowest Selling Item", 
        each 
          let
            a = Table.Group(_, {"Item"}, {"soma", each List.Sum(_[Sale])}), 
            b = Table.SelectRows(a, each [soma] = List.Min(a[soma]))[Item]
          in
            Text.Combine(b, ", ")
      }
    }
  )
in
  gp

Power Query solution 5 for Weekday Weekend Sales Report, proposed by Hussein SATOUR:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  DOW = Table.AddColumn(
    Source, 
    "Day of Week", 
    each if Date.DayOfWeek([Date]) > 4 then "Weekend" else "weekday", 
    Int64.Type
  ), 
  Group1 = Table.Group(
    DOW, 
    {"Item", "Day of Week"}, 
    {{"Total", each List.Sum([Sale]), type number}}
  ), 
  Group2 = Table.Group(
    Group1, 
    {"Day of Week"}, 
    {
      {"Total", each List.Sum([Total]), type number}, 
      {"all", each _, type table [Item = text, Day of Week = number, Total = number]}, 
      {"min", each List.Min([Total]), type number}, 
      {"max", each List.Max([Total]), type number}
    }
  ), 
  AddLow = Table.AddColumn(
    Group2, 
    "Lowest Selling", 
    each 
      let
        a = [min], 
        b = Table.SelectRows([all], each ([Total] = a))[Item]
      in
        Text.Combine(b, ",")
  ), 
  AddHigh = Table.AddColumn(
    AddLow, 
    "Highest Selling", 
    each 
      let
        a = [max], 
        b = Table.SelectRows([all], each ([Total] = a))[Item]
      in
        Text.Combine(b, ",")
  ), 
  Remov = Table.RemoveColumns(AddHigh, {"all", "min", "max"})
in
  Remov

Power Query solution 6 for Weekday Weekend Sales Report, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddDayType = Table.AddColumn(
    Source, 
    "DayType", 
    each if Text.StartsWith(Date.DayOfWeekName([Date]), "S") then "Weekend" else "Weekday"
  ), 
  AddItemSales = Table.Group(
    AddDayType, 
    {"DayType", "Item"}, 
    {{"ItemSales", each List.Sum([Sale]), type number}}
  ), 
  Group = Table.Group(
    AddItemSales, 
    {"DayType"}, 
    {
      {"MaxItemSale", each List.Max([ItemSales]), type number}, 
      {"MinItemSales", each List.Min([ItemSales]), type number}, 
      {"All", each _, type table}
    }
  ), 
  Expand = Table.ExpandTableColumn(Group, "All", {"Item", "ItemSales"}, {"Item", "ItemSales"}), 
  AddHigh = Table.AddColumn(
    Expand, 
    "HighestItem", 
    each if [ItemSales] = [MaxItemSale] then [Item] else null
  ), 
  AddLow = Table.AddColumn(
    AddHigh, 
    "LowestItem", 
    each if [ItemSales] = [MinItemSales] then [Item] else null
  ), 
  ReGroup = Table.Group(
    AddLow, 
    {"DayType"}, 
    {
      {"Total Sales", each List.Sum([ItemSales]), type number}, 
      {"Highest Selling Item", each Text.Combine([HighestItem], ", "), type text}, 
      {"Lowest Selling Item", each Text.Combine([LowestItem], ", "), type text}
    }
  )
in
  ReGroup

Power Query solution 7 for Weekday Weekend Sales Report, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn, 
  G = Table.Group, 
  E = Table.ExpandListColumn, 
  C = Text.Combine, 
  a = A(S, "D", each Date.DayOfWeek([Date])), 
  b = A(a, "W", each if [D] = 5 or [D] = 6 then "Weekend" else "Weekday"), 
  L1 = Table.ToColumns(G(b, {"W"}, {"S", each List.Sum([Sale])})), 
  c = G(b, {"W", "Item"}, {"S", each List.Sum([Sale])}), 
  d = G(c, {"W"}, {"G", each Table.MaxN(G([[Item], [S]], {"S"}, {"M", each _}), "S", 1)[M]}), 
  L2 = {Table.TransformColumns(E(d, "G"), {"G", each C([Item], ", ")})[G]}, 
  e = G(c, {"W"}, {"G", each Table.MinN(G([[Item], [S]], {"S"}, {"M", each _}), "S", 1)[M]}), 
  L3 = {Table.TransformColumns(E(e, "G"), {"G", each C([Item], ", ")})[G]}, 
  Sol = Table.RenameColumns(
    Table.FromColumns(L1 & L2 & L3), 
    {
      {"Column1", "Day Type"}, 
      {"Column2", "Total Sales"}, 
      {"Column3", "Highest Selling Item"}, 
      {"Column4", "Lowest Selling Item"}
    }
  )
in
  Sol

Power Query solution 8 for Weekday Weekend Sales Report, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData170"]}[Content], 
  Add_DoW = Table.AddColumn(
    Table.TransformColumnTypes(Source, {{"Date", type date}}), 
    "DoW", 
    each if (Date.DayOfWeek([Date], Day.Monday) >= 5) then "Weekend" else "Weekday"
  ), 
  Group = Table.Group(
    Add_DoW, 
    "DoW", 
    {
      {"Total Sales", each List.Sum([Sale])}, 
      {
        "G", 
        each 
          let
            G = Table.Sort(Table.Group(_, "Item", {"Sales", each List.Sum([Sale])}), "Sales"), 
            fxGetItems = (lf) =>
              Text.Combine(
                List.Reverse(Table.SelectRows(G, each [Sales] = lf(G[Sales]))[Item]), 
                ", "
              )
          in
            [HSI = fxGetItems(List.Last), LSI = fxGetItems(List.First)]
      }
    }
  ), 
  Expand = Table.ExpandRecordColumn(
    Group, 
    "G", 
    {"HSI", "LSI"}, 
    {"Highest Selling Item", "Lowest Selling Item"}
  )
in
  Expand

Power Query solution 9 for Weekday Weekend Sales Report, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    S, 
    "Weekday/end", 
    each if Date.DayOfWeek([Date]) = 0 or Date.DayOfWeek([Date]) = 6 then "Weekend" else "Weekday"
  ), 
  T1 = Table.Group(A, {"Item", "Weekday/end"}, {{"T", each List.Sum([Sale]), type number}}), 
  T2 = Table.Group(
    T1, 
    {"Weekday/end"}, 
    {
      {"TotalSales", each List.Sum([T]), type number}, 
      {"Min", each List.Min([T]), type number}, 
      {"Max", each List.Max([T]), type number}
    }
  ), 
  H = Table.AddColumn(
    T2, 
    "Highest Selling Item", 
    each Text.Combine(
      Table.SelectRows(T1, (X) => X[#"Weekday/end"] = [#"Weekday/end"] and X[T] = [Max])[Item], 
      ","
    )
  ), 
  Lo = Table.AddColumn(
    H, 
    "Lowest Item Selling", 
    each Text.Combine(
      Table.SelectRows(T1, (X) => X[#"Weekday/end"] = [#"Weekday/end"] and X[T] = [Min])[Item], 
      ","
    )
  ), 
  Sol = Table.SelectColumns(
    Lo, 
    {"Weekday/end", "TotalSales", "Highest Selling Item", "Lowest Item Selling"}
  )
in
  Sol

Power Query solution 10 for Weekday Weekend Sales Report, proposed by Yaroslav Drohomyretskyi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.ExpandRecordColumn(
    Table.Group(
      Table.AddColumn(
        Source, 
        "Day Type", 
        each if Date.DayOfWeek([Date], Day.Monday) < 5 then "Weekday" else "Weekend"
      ), 
      "Day Type", 
      {
        {"Total Sales", each List.Sum([Sale])}, 
        {
          "Rows", 
          each 
            let
              Rows = Table.Sort(Table.Group(_, "Item", {"Sales", each List.Sum([Sale])}), "Sales"), 
              magic = (lf) =>
                Text.Combine(
                  List.Reverse(Table.SelectRows(Rows, each [Sales] = lf(Rows[Sales]))[Item]), 
                  ", "
                )
            in
              [High = magic(List.Last), Low = magic(List.First)]
        }
      }
    ), 
    "Rows", 
    {"High", "Low"}, 
    {"Highest Selling Item", "Lowest Selling Item"}
  )
in
  Result

Power Query solution 11 for Weekday Weekend Sales Report, proposed by Glyn Willis:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Date", type date}, {"Item", type text}, {"Sale", Int64.Type}}
  ), 
  Custom1 = Table.AddKey(#"Changed Type", {"Date"}, true), 
  Custom2 = Table.FromColumns(
    {
      {"Weekdays", "Weekends"}, 
      Table.Partition(Custom1, "Date", 2, each Int64.From(Date.DayOfWeek(_, Day.Monday) > 4))
    }, 
    {"Day Type", "d"}
  ), 
  #"Added Custom" = Table.AddColumn(
    Custom2, 
    "r", 
    each [
      Total Sales = List.Sum([d][Sale]), 
      Grp = Table.Group([d], {"Item"}, {{"S", each List.Sum([Sale])}}), 
      Highest Selling Item = 
        let
          max = List.Max(Grp[S])
        in
          Text.Combine(Table.SelectRows(Grp, (x) => x[S] = max)[Item], ", "), 
      Lowest Selling Item = 
        let
          min = List.Min(Grp[S])
        in
          Text.Combine(Table.SelectRows(Grp, (x) => x[S] = min)[Item], ", ")
    ]
  )[[Day Type], [r]], 
  #"Expanded r" = Table.ExpandRecordColumn(
    #"Added Custom", 
    "r", 
    {"Total Sales", "Highest Selling Item", "Lowest Selling Item"}, 
    {"Total Sales", "Highest Selling Item", "Lowest Selling Item"}
  )
in
  #"Expanded r"

Power Query solution 12 for Weekday Weekend Sales Report, proposed by Arnaud Duvernois:

let
  Source = Excel.CurrentWorkbook(){[Name = "tSource170"]}[Content], 
  AddDayType = Table.AddColumn(
    Source, 
    "Day type", 
    each if Date.DayOfWeek([Date], Day.Monday) > 4 then "Weekend" else "Weekday"
  ), 
  GroupBy = Table.Group(
    AddDayType, 
    {"Day type"}, 
    {
      {"Total Sales", each List.Sum(_[Sale])}, 
      {
        "Highest selling item", 
        each 
          let
            a = Table.Group(_, {"Day type", "Item"}, {{"Highest Item", (t) => List.Sum(t[Sale])}}), 
            b = Table.SelectRows(a, (s) => s[Highest Item] = List.Max(a[Highest Item]))[Item]
          in
            Text.Combine(b, ", ")
      }, 
      {
        "Lowest selling item", 
        each 
          let
            a = Table.Group(_, {"Day type", "Item"}, {{"Lowest Item", (t) => List.Sum(t[Sale])}}), 
            b = Table.SelectRows(a, (s) => s[Lowest Item] = List.Min(a[Lowest Item]))[Item]
          in
            Text.Combine(b, ", ")
      }
    }
  )
in
  GroupBy

Solving the challenge of Weekday Weekend Sales Report with Excel

Excel solution 1 for Weekday Weekend Sales Report, proposed by Bo Rydobon 🇹🇭:

=LET(g,GROUPBY(HSTACK("Week"&IF(WEEKDAY(A2:A92,2)<6,"day","end"),B2:B92),C2:C92,SUM,,0),h,GROUPBY(CHOOSECOLS(g,1,3),CHOOSECOLS(g,2,3),HSTACK(ARRAYTOTEXT,SUM),,0),
DROP(GROUPBY(TAKE(h,,1),CHOOSECOLS(h,4,3,3),HSTACK(SUM,LAMBDA(x,@TAKE(x,-1)),SINGLE),,0),2))

Excel solution 2 for Weekday Weekend Sales Report, proposed by محمد حلمي:

=LET(b,B2:B92,e,C2:C92,n,UNIQUE(b),j,WEEKDAY(A2:A92,3),k,LAMBDA(x,
MAP(n,LAMBDA(a,SUM(e*(b=a)*IF(x,j<5,j>4))))),r,LAMBDA(x,[w],TEXTJOIN(", ",,
REPT(n,x=IF(w,MAX(x),MIN(x))))),VSTACK(HSTACK("Weekday",SUM(e*(j<5)),r(k(1),1),r(k(1))),HSTACK("Weekend",SUM(e*(j>4)),r(k(0),1),r(k(0)))))

Excel solution 3 for Weekday Weekend Sales Report, proposed by 🇰🇷 Taeyong Shin:

=LET(
 i, B2:B92, s, C2:C92, w, "Week" & IF(WEEKDAY(A2:A92, 2) < 6, "day", "end"), u, UNIQUE(i),
 fn, LAMBDA(x,f,
 LET(b, w = x, a, MMULT(TRANSPOSE((i = TOROW(u)) * b), s), ARRAYTOTEXT(FILTER(u, a = f(a))))
 ),
 MAP(IF({1,1,1,1}, UNIQUE(w)), IF({1;1}, {1,2,3,4}), LAMBDA(a,n,
 SWITCH(n, 2, SUM(s * (w = a)), 3, fn(a, MAXʎ), 4, fn(a, MINʎ), a)
 ))
)
Eta Reduction LAMBDA has too many bugs.
If the abstracted function works but Eta does not, it is a bug.
MINʎ = LAMBDA(x, MIN(x));
MAXʎ = LAMBDA(x, MAX(x))

Excel solution 4 for Weekday Weekend Sales Report, proposed by 🇰🇷 Taeyong Shin:

=LET(
 g, GROUPBY(HSTACK("Week" & IF(WEEKDAY(A2:A92, 2) < 6, "day", "end"), B2:B92), C2:C92, SUM, , 0, -3),
 h, GROUPBY(CHOOSECOLS(g, 1, 3), CHOOSECOLS(g, 2, 3), HSTACK(ARRAYTOTEXT, SUM),, 0),
 DROP(GROUPBY(TAKE(h,, 1), CHOOSECOLS(h, 4, 3, 3), HSTACK(SUM, LAMBDA(x, @TAKE(x,-1)), SINGLE), , 0), 2)
)

Excel solution 5 for Weekday Weekend Sales Report, proposed by Julian Poeltl:

=LET(T,A2:C92,D,CHOOSECOLS(T,1),WD,WEEKDAY(D,2)<6,M,MAKEARRAY(2,4,LAMBDA(A,B,LET(TT,IF(A=1,FILTER(T,WD),FILTER(T,WD=FALSE)),IU,SORT(UNIQUE(CHOOSECOLS(TT,2))),QIU,MAP(IU,LAMBDA(A,SUM(FILTER(CHOOSECOLS(TT,3),CHOOSECOLS(TT,2)=A)))),S,SUM(QIU),SWITCH(B,1,IF(A=1,"Weekday","Weekend"),2,S,3,TEXTJOIN(", ",,FILTER(IU,QIU>=MAX(QIU))),4,TEXTJOIN(", ",,FILTER(IU,QIU<=MIN(QIU))))))),VSTACK(HSTACK("Day Type","Total Sales","Highest Selling Item","L&owest Selling Item"),M))

Excel solution 6 for Weekday Weekend Sales Report, proposed by Julian Poeltl:

=LET(T,A2:C92,D,CHOOSECOLS(T,1),I,CHOOSECOLS(T,2),Q,CHOOSECOLS(T,3),WD,WEEKDAY(D,2)<6,WDS,SUM(Q*WD),WES,SUM(Q*(WD=FALSE)),IU,SORT(UNIQUE(I)),HW,TEXTJOIN(", ",,FILTER(IU,LET(M,(MAP(IU,LAMBDA(A,SUM(FILTER(Q,WD*(I=A)))))),M>=MAX(M)))),LW,TEXTJOIN(", ",,FILTER(IU,LET(M,(MAP(IU,LAMBDA(A,SUM(FILTER(Q,WD*(I=A)))))),M<=MIN(M)))),HE,TEXTJOIN(", ",,FILTER(IU,LET(M,(MAP(IU,LAMBDA(A,SUM(FILTER(Q,(WD=FALSE)*(I=A)))))),M>=MAX(M)))),LE,TEXTJOIN(", ",,FILTER(IU,LET(M,(MAP(IU,LAMBDA(A,SUM(FILTER(Q,(WD=FALSE)*(I=A)))))),M<=MIN(M)))),VSTACK(HSTACK("Day Type","Total Sales","Highest Selling Item","Lowest Selling Item"),HSTACK("Weekday",WDS,HW,LW),HSTACK("Weekend",WES,HE,LE)))

Excel solution 7 for Weekday Weekend Sales Report, proposed by Oscar Mendez Roca Farell:

=DROP(REDUCE("", {0, 1}, LAMBDA(i, x, LET(wd, INT(WEEKDAY(A2:A92, 2)/6), f, FILTER(B2:C92, wd=x), u, SORT(UNIQUE(B2:B92)), b, BYCOL(IFS(TAKE(f,,1)=TOROW(u), DROP(f, , 1), 1, ), LAMBDA(c, SUM(c))), H, LAMBDA(j, ARRAYTOTEXT(TOCOL(IFS(XMATCH(b, AGGREGATE({14, 15}, 4, b, 1))=j, TOROW(u)), 2))), VSTACK(i,  HSTACK("Week" & IF(x, "end", "day"), SUM(f), H(1), H(2)))))), 1)

Excel solution 8 for Weekday Weekend Sales Report, proposed by Sunny Baggu:

=HSTACK(
 {"Weekday"; "Weekend"},
 LET(
 _wend, BYROW(WEEKDAY(A2:A92) = {7, 1}, LAMBDA(a, OR(a))),
 _wday, NOT(_wend),
 DROP(
 REDUCE(
 "",
 {1; 2},
 LAMBDA(a, v,
 VSTACK(
 a,
 LET(
 cri, INDEX(HSTACK(_wday, _wend), , v),
 _ui, UNIQUE(B2:B92),
 _sum, MAP(_ui, LAMBDA(x, SUM((B2:B92 = x) * cri * C2:C92))),
 _maxsum, MAX(_sum),
 _minsum, MIN(_sum),
 HSTACK(
 TOCOL(BYCOL(C2:C92 * cri, LAMBDA(x, SUM(x)))),
 ARRAYTOTEXT(FILTER(_ui, _sum = _maxsum)),
 ARRAYTOTEXT(FILTER(_ui, _sum = _minsum))
 )
 )
 )
 )
 ),
 1
 )
 )
)

Excel solution 9 for Weekday Weekend Sales Report, proposed by LEONARD OCHEA 🇷🇴:

=LET(p,PIVOTBY(IF(WEEKDAY(A2:A92,2)<6,"Weekday","Weekend"), B2:B92,C2:C92,SUM,0,0,,0),d,DROP(p,1,1),h,DROP(TAKE(p,1),,1),B,LAMBDA(i,BYROW(d,LAMBDA(x,ARRAYTOTEXT(FILTER(h,x=IF(i=1,MAX(x),MIN(x))))))),HSTACK(DROP(TAKE(p,,1),1),BYROW(d,SUM),B(1),B(2)))

Excel solution 10 for Weekday Weekend Sales Report, proposed by Md. Zohurul Islam:

=LET(u,A2:A92,v,B2:B92,w,C2:C92,
hdr,HSTACK("Total Sales","Highest Selling Item","Lowest Selling Item"),
wk,IF(WEEKDAY(u,2)<=5,"Weekday","Weekend"),
p,PIVOTBY(wk,v,w,SUM,0,0,,0),
wkdy,DROP(TAKE(p,,1),1),
q,REDUCE(hdr,wkdy,LAMBDA(x,y,LET(
 a,FILTER(DROP(p,,1),TAKE(p,,1)=y),
 b,ARRAYTOTEXT(FILTER(DROP(TAKE(p,1),,1),a=MAX(a))),
 c,ARRAYTOTEXT(FILTER(DROP(TAKE(p,1),,1),a=MIN(a))),
 d,HSTACK(SUM(a),b,c),
 e,VSTACK(x,d),e))),
z,HSTACK(VSTACK("Day Type",wkdy),q),
z)

Excel solution 11 for Weekday Weekend Sales Report, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(j,B2:B92,i,C2:C92,d,BYROW(IFERROR(SEARCH({"sat","sun"},TEXT(A2:A92,"ddd")),0),LAMBDA(a,SUM(a))),LET(x,FILTER(i,d=0),y,FILTER(j,d=0),z,FILTER(j,d=1),c,FILTER(i,d=1),VSTACK(HSTACK("Weekday",SUM(FILTER(i,d=0)),TEXTJOIN(", ",,FILTER(y,MAX(FILTER(i,d=0))=x)),TEXTJOIN(", ",,FILTER(y,MIN(FILTER(i,d=0))=x))),HSTACK("Weekend",SUM(FILTER(i,d=1)),TEXTJOIN(", ",,FILTER(z,MAX(FILTER(i,d=1))=c)),TEXTJOIN(", ",,FILTER(z,MIN(FILTER(i,d=1))=c))))))

Excel solution 12 for Weekday Weekend Sales Report, proposed by Burhan Cesur:

=LET(wday,BYCOL(((TOROW(SORT(UNIQUE(B2:B92)))=B2:B92)*C2:C92)*(WEEKDAY(A2:A92,2)<6),SUM),
 wend,BYCOL(((TOROW(SORT(UNIQUE(B2:B92)))=B2:B92)*C2:C92)*(WEEKDAY(A2:A92,2)>5),SUM),
 wdtotals,BYCOL(C2:C92,LAMBDA(x,SUM(IF(WEEKDAY(OFFSET(x,,-2),2)<6,x)))),
 wetotals,BYCOL(C2:C92,LAMBDA(x,SUM(IF(WEEKDAY(OFFSET(x,,-2),2)>5,x)))),
 wdhsi,TEXTJOIN(", ",,FILTER(TOROW(SORT(UNIQUE(B2:B92))),MAX(wday)=wday)),
 wehsi,TEXTJOIN(", ",,FILTER(TOROW(SORT(UNIQUE(B2:B92))),MAX(wend)=wend)),
 wdlsi,TEXTJOIN(", ",,FILTER(TOROW(SORT(UNIQUE(B2:B92))),MIN(wday)=wday)),
 welsi,TEXTJOIN(", ",,FILTER(TOROW(SORT(UNIQUE(B2:B92))),MIN(wend)=wend)),
 headers,HSTACK("Day Type","Total Sales","Highest Selling Item","Lowest Selling Item"),
 VSTACK(headers,HSTACK("Weekday",wdtotals,wdhsi,wdlsi),HSTACK("Weekend",wetotals,wehsi,welsi)))

Excel solution 13 for Weekday Weekend Sales Report, proposed by Duncan Williamson:

=SUM(FILTER(Table1[[Sale]:[Weekend]],Table1[Weekday]=1)), J2=XLOOKUP(MAX(FILTER(Table1[Sale],Table1[Weekday]=1)),Table1[Sale],Table1[Item]), k2=XLOOKUP(MIN(FILTER(Table1[Sale],Table1[Weekday]=1)),Table1[Sale],Table1[Item]), I3=SUM(FILTER(Table1[[Sale]:[Weekend]],Table1[Weekend]=1)), J3=XLOOKUP(MAX(FILTER(Table1[Sale],Table1[Weekend]=1)),Table1[Sale],Table1[Item]), K3=XLOOKUP(MIN(FILTER(Table1[Sale],Table1[Weekend]=1)),Table1[Sale],Table1[Item],,,1)&", "&XLOOKUP(MIN(FILTER(Table1[Sale],Table1[Weekend]=1)),Table1[Sale],Table1[Item],,,-1)

Solving the challenge of Weekday Weekend Sales Report with Python in Excel

Python in Excel solution 1 for Weekday Weekend Sales Report, proposed by Alejandro Campos:

df_input = xl("A1:C92", headers=True)
df_input['Rev'] = [int(i.strftime('%w')) for i in df_input['Date']]
df_weekday, df_weekend = df_input[df_input.Rev.between(1, 5)], df_input[df_input.Rev.isin([0, 6])]
group_sales = lambda df: df.groupby('Item')['Sale'].sum()
dfWkday, dfWkend = group_sales(df_weekday), group_sales(df_weekend)
pd.DataFrame({
 'Day Type': ['Weekday', 'Weekend'],
 'Total Sales': [dfWkday.sum(), dfWkend.sum()],
 'Highest Selling Item': [",".join(dfWkday[dfWkday == dfWkday.max()].index), ",".join(dfWkend[dfWkend == dfWkend.max()].index)],
 'Lowest Selling Item': [",".join(dfWkday[dfWkday == dfWkday.min()].index), ",".join(dfWkend[dfWkend == dfWkend.min()].index)]
})

Python in Excel solution 2 for Weekday Weekend Sales Report, proposed by ferhat CK:

Phyton in Excel 
al=xl("A1:C92", headers=True)
al['Gun']=[int(i.strftime('%w')) for i in al['Date']]
df=al[(al.Gun<6) & (al.Gun>0)] 
df2=df.groupby('Item')['Sale'].sum()
df3=al[(al.Gun == 0) | (al.Gun == 6)]
df4=df3.groupby('Item')['Sale'].sum()
pd.DataFrame({'Day Type':['Weekday','Weekend'],'Total Sales':[df2.sum(),df4.sum()],'Highest Selling Item':[",".join(df2[df2 == df2.max()].index.tolist()),",".join(df4[df4 == df4.max()].index.tolist())],'Lowest Selling Item':[",".join(df2[df2 == df2.min()].index.tolist()),",".join(df4[df4 == df4.min()].index.tolist())]})

Solving the challenge of Weekday Weekend Sales Report with R

R solution 1 for Weekday Weekend Sales Report, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_170.xlsx", range = "A1:C92")
test = read_excel("Power Query/PQ_Challenge_170.xlsx", range = "E1:H3")
result = input %>%
 mutate(week_part = ifelse(wday(Date) %in% c(1, 7), "Weekend", "Weekday")) %>%
 summarise(total = sum(Sale), 
 .by = c(week_part, Item)) %>%
 mutate(min = min(total),
 max = max(total),
 full_total = sum(total),
 .by = c(week_part)) %>%
 filter(total == min | total == max) %>%
 mutate(min_max = ifelse(total == min, "min", "max")) %>%
 select(-c(total, min, max)) %>%
 pivot_wider(names_from = min_max, values_from = Item, values_fn = list(Item = list)) %>%
 mutate(min = map_chr(min, ~paste(.x, collapse = ", ")),
 max = map_chr(max, ~paste(.x, collapse = ", "))) 
colnames(result) <- colnames(test)

Solving the challenge of Weekday Weekend Sales Report with DAX

DAX solution 1 for Weekday Weekend Sales Report, proposed by Zoran Milokanović:

DEFINE
VAR D = SUMMARIZE(ADDCOLUMNS(Input, "Day Type", IF(MOD(WEEKDAY(Input[Date]), 6) = 1, "Weekend", "Weekday")), [Day Type], Input[Item], "Sale", SUM(Input[Sale]))
VAR M = GROUPBY(D, [Day Type], "Max Sales", MAXX(CURRENTGROUP(), [Sale]), "Min Sales", MINX(CURRENTGROUP(), [Sale]))
EVALUATE
ADDCOLUMNS(
 GROUPBY(D, [Day Type], "Total Sales", SUMX(CURRENTGROUP(), [Sale])),
 "Highest Selling Item", VAR DT = [Day Type]
 RETURN CONCATENATEX(FILTER(D, AND([Sale] = SELECTCOLUMNS(FILTER(M, [Day Type] = DT), "S", [Max Sales]), [Day Type] = DT)), Input[Item], ", "),
 "Lowest Selling Item", VAR DT = [Day Type]
 RETURN CONCATENATEX(FILTER(D, AND([Sale] = SELECTCOLUMNS(FILTER(M, [Day Type] = DT), "S", [Min Sales]), [Day Type] = DT)), Input[Item], ", ")
)

Solving the challenge of Weekday Weekend Sales Report with Power Query

Solving the challenge of Weekday Weekend Sales Report with Excel

Solving the challenge of Weekday Weekend Sales Report with Python in Excel

Solving the challenge of Weekday Weekend Sales Report with R

Solving the challenge of Weekday Weekend Sales Report with DAX

Leave a Reply Cancel reply