In the Question table, daily sales information is provided. Extract the top two products for each month and their share of total sales per month. For example, in Month 3, products B, A, D, and C were sold in quantities of 67, 16, 21, and 24, respectively. Therefore, the top two products for this month are B and C, with sales shares of 52% and 19%, respectively, while the remaining 29% of sales are attributed to other products.
📌 Challenge Details and Links
Challenge Number: 96
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Top Products! with Power Query
Power Query solution 1 for Top Products!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Combine(
Table.Group(
Table.Group(
Table.TransformColumns(Source, {"Date", Date.Month}),
{"Date", "Product"},
{"Quantity", each List.Sum([Quantity])}
),
"Date",
{
"A",
each
let
t = Table.Sort(_, {"Quantity", 1}),
p = t[Product],
q = t[Quantity],
c = List.Count(p)
in
List.Transform(
{0 .. {2, c}{Byte.From(c < 2)}},
(m) =>
let
f = Byte.From(m > 1)
in
{
[Date]{0},
{p{m}, "Other"}{f},
{q{m}, List.Sum(List.Skip(q, m))}{f} / List.Sum(q)
}
)
}
)[A]
),
{"Month", "Product", "% of Month sales"}
)
in
SPower Query solution 2 for Top Products!, proposed by 🇵🇪 Ned Navarrete C.:
let
S = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
M = Table.TransformColumns(S, {{"Date", Date.Month}}),
G = Table.Group(
M,
{"Date"},
{
{
"X",
each [
a = List.Sum(_[Quantity]),
b = Table.Group(_, {"Date", "Product"}, {{"P", each List.Sum([Quantity])}}),
c = Table.TransformColumns(b, {{"P", each _ / a}}),
d = Table.Sort(c, {"P", 1}),
e = Table.FirstN(d, 2),
f = Table.ToRecords(e),
g = [Date = [Date]{0}, Product = "Other", P = 1 - List.Sum(e[P])],
h = Table.FromRecords(f & {g})
][h]
}
}
),
R = Table.ExpandTableColumn(
G[[X]],
"X",
{"Date", "Product", "P"},
{"Month", "Product", "% of Month sales"}
)
in
RPower Query solution 3 for Top Products!, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ExtractMo = Table.TransformColumns(Source, {{"Date", Date.Month}}),
Group = Table.Group(ExtractMo, {"Date", "Product"}, {{"Q", each List.Sum([Quantity])}}),
ReGroup = Table.Group(Group, {"Date"}, {{"MonSales", each List.Sum([Q])}, {"All", each _}}),
AddRank = Table.AddColumn(
ReGroup,
"RankCol",
each Table.AddRankColumn(
[All],
"Rank",
{"Q", Order.Descending},
[RankKind = RankKind.Competition]
)
),
Expand = Table.RemoveColumns(
Table.ExpandTableColumn(AddRank, "RankCol", {"Product", "Q", "Rank"}, {"Prod", "Q", "Rank"}),
"All"
),
AddOther = Table.AddColumn(Expand, "Product", each if [Rank] <= 2 then [Prod] else "Other"),
SumGT2 = Table.RenameColumns(
Table.Group(AddOther, {"Date", "MonSales", "Product"}, {{"Q", each List.Sum([Q])}}),
{"Date", "Month"}
),
AddPct = Table.RemoveColumns(
Table.AddColumn(SumGT2, "% Mon Sales", each Number.Round([Q] / [MonSales], 2), Percentage.Type),
{"MonSales", "Q"}
)
in
AddPctPower Query solution 4 for Top Products!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
Group1 = Table.Group(Month, {"Month", "Product"}, {"Qty", each List.Sum([Quantity])}),
Group2 = Table.Group(
Group1,
"Month",
{
"All",
each [
M = Table.MaxN(_, "Qty", 2)[[Product], [Qty]],
T = Table.TransformColumns(M, {"Qty", (f) => f / List.Sum([Qty])}),
O = #table({"Product", "Qty"}, {{"Other", 1 - List.Sum(T[Qty])}}),
R = T & O
][R],
type table [Product, Qty = Percentage.Type]
}
),
Return = Table.ExpandTableColumn(Group2, "All", {"Product", "Qty"}, {"Product", "Percentage"})
in
ReturnPower Query solution 5 for Top Products!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
Group = Table.Group(Month, "Month", {{"All", each
let
a = Table.Group(_ [[Product],[Quantity]], "Product", {"Quantity", each
List.Sum([Quantity])}),
b = List.Sum([Quantity]),
c = List.Transform(Table.ToRows(a), each {_{0},Number.ToText(_{1}/b, "p0")}),
d = List.Sort(c, {each Number.FromText(_{1}),1}),
e = List.FirstN(d,2),
f = {"Other", Number.ToText(List.Sum(List.Transform(List.Skip(d,2),
each Number.From(_{1}))), "P0")},
g = Table.FromRows(e&{f}, {"Product", "% of Month sales"})
in g}}),
Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
SolPower Query solution 6 for Top Products!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Mth = Table.TransformColumns(Source, {"Date", Date.Month}),
Per = Table.AddColumn(
Mth,
"Percent",
each List.Sum(
Table.SelectRows(Mth, (x) => x[Date] = [Date] and x[Product] = [Product])[Quantity]
)
/ List.Sum(Table.SelectRows(Mth, (x) => x[Date] = [Date])[Quantity])
),
Group = Table.Group(
Per,
{"Date"},
{
"All",
each Table.Group(
Table.AddColumn(
Table.AddIndexColumn(Table.Distinct(Table.Sort(_, {"Percent", 1}), "Product"), "I", 1, 1),
"Add",
each if [I] > 2 then "Other" else [Product]
),
{"Add"},
{"All", each Number.Round(List.Sum([Percent]), 2)}
)
}
),
Xpand = Table.ExpandTableColumn(Group, "All", {"Add", "All"}, {"Product", "% of Month sales"}),
Type = Table.TransformColumnTypes(Xpand, {{"% of Month sales", Percentage.Type}}),
Rename = Table.RenameColumns(Type, {{"Date", "Month"}})
in
RenamePower Query solution 7 for Top Products!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.TransformColumns(S,{{"Date", Date.Month, Int64.Type}}),
B = Table.Group(A, {"Date"}, {{"T", each _, type table [Date=number, Product=text, Quantity=number]}}),
C=(x)=>
let
a = Table.Group(x, {"Product"}, {{"Q", each List.Sum([Quantity]), type number}}),
b = Table.Sort(a,{{"Q", Order.Descending}}),
c = Table.AddIndexColumn(b, "I", 1, 1, Int64.Type),
d = Table.AddColumn(c, "%", each [Q]/List.Sum(c[Q])),
e = Table.AddColumn(d, "P", each if [I]<=2 then [Product] else "Other"),
f = Table.Group(e, {"P"}, {{"% of Month Sales", each List.Sum([#"%"]), type number}})
in
f,
D = Table.AddColumn(B, "C", each C([T])),
E = Table.SelectColumns(D,{"Date", "C"}),
F = Table.ExpandTableColumn(E, "C", {"P", "% of Month Sales"}, {"P", "% of Month Sales"}),
G = Table.TransformColumnTypes(F,{{"% of Month Sales", Percentage.Type}}),
H = Table.TransformColumns(G,{{"% of Month Sales", each Number.Round(_, 2), Percentage.Type}}),
I = Table.RenameColumns(H,{{"Date", "Month"}, {"P", "Product"}})
in
IPower Query solution 8 for Top Products!, proposed by Szabolcs Phraner:
let Source = ,
//create Month column while setting data types
PrepareRows = Table.FromRecords(
Table.TransformRows( Source,
each
[Month = Date.Month( Date.FromText([Date],[Format = "d/M/yyyy"]) ), Product = [Product], Quantity = Int64.From([Quantity])]
)
, type table [Month = Int64.Type, Product = Text.Type, Quantity = Int64.Type ]
),
Aggregate_Quantity = Table.Group(PrepareRows, {"Month", "Product"}, {{"Quantity", each List.Sum([Quantity]), Int64.Type}}),
GroupMonths = Table.Group(Aggregate_Quantity, {"Month"}, {{"Result",
each
[
total = List.Sum([Quantity]),
top_prod = Table.MaxN(_,"Quantity",2),
insert_other = Table.InsertRows(top_prod,2, {[Month = null, Product = "Other", Quantity = total - List.Sum(top_prod[Quantity]) ]}),
calc_percentage = Table.AddColumn(insert_other, "% of Month sales", each Number.Round( [Quantity] / total,2) , Percentage.Type )
] [ calc_percentage]
,type table [Product = text, #"% of Month sales" = Percentage.Type]
}}),
Expand_ResultTable = Table.ExpandTableColumn(GroupMonths, "Result", {"Product", "% of Month sales"}, {"Product", "% of Month sales"})
in
Expand_ResultTableSolving the challenge of Top Products! with Excel
Excel solution 1 for Top Products!, proposed by Bo Rydobon 🇹🇭:
=LET(
m,
MONTH(
B3:B20
),
p,
C3:C20,
q,
D3:D20,
REDUCE(
I2:K2,
UNIQUE(
m
),
LAMBDA(
a,
i,
LET(
g,
GROUPBY(
p,
q,
SUM,
,
0,
-2,
m=i
),
h,
TAKE(
g,
2
),
k,
IFERROR(
h/SUM(
g
),
h
),
VSTACK(
a,
IFNA(
HSTACK(
i,
VSTACK(
k,
HSTACK(
"Other",
1-SUM(
k
)
)
)
),
i
)
)
)
)
)
)Excel solution 2 for Top Products!, proposed by 🇰🇷 Taeyong Shin:
=LET(
h,
SORT(
GROUPBY(
HSTACK(
MONTH(
B3:B20
),
C3:C20
),
D3:D20,
SUM,
,
0
),
{1,
3},
{1,
-1}
), c,
TAKE(
h,
,
1
), s,
SEQUENCE(
ROWS(
c
)
), n,
MAP(MMULT((s >= TOROW(
s
)) * (c = TOROW(
c
)),
SIGN(
c
)),
LAMBDA(
x,
MIN(
x,
3
)
)), f,
LAMBDA(
v,
f,
DROP(
DROP(
PIVOTBY(
c,
n,
v,
f,
,
-1,
,
,
,
,
1
),
2,
-1
),
,
1
)
), HSTACK( TOCOL(
IFNA(
UNIQUE(
c
),
TAKE(
h,
1
)
)
), TOCOL(
IF(
{1,
1,
0},
f(
INDEX(
h,
,
2
),
CONCAT
),
"Other"
)
), TOCOL(
f(
TAKE(
h,
,
-1
),
PERCENTOF
)
) )
)Excel solution 3 for Top Products!, proposed by محمد حلمي:
=REDUCE(I2:K2,
UNIQUE(
MONTH(
B3:B20
)
),
LAMBDA(a,
v,VSTACK(a,
LET(b,
B3:B20,
c,
C3:C20,
d,
--(v&-2024),i,
UNIQUE(
c
),
w,
SUMIFS(
D3:D20,
c,
i,
b,
">="&d,
b,
"<"&
EDATE(
d,
1
)
),
r,
IFNA(
SORT(
HSTACK(
v,
i,
w/SUM(
w
)
),
3,
-1
),
v
),VSTACK(
TAKE(
r,
2
),
HSTACK(
v,
"Other",
SUM(
DROP(
r,
2,
1
)
)
)
)))))Excel solution 4 for Top Products!, proposed by Oscar Mendez Roca Farell:
=LET(
m,
MONTH(
B3:B20
),
REDUCE(
I2:K2,
UNIQUE(
m
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
C3:D20,
m=x
),
p,
TAKE(
f,
,
1
),
u,
TOROW(
UNIQUE(
p
)
),
m,
MMULT(
TOROW(
DROP(
f,
,
1
)
),
N(
p=u
)
),
k,
LARGE(
m,
{1,
2}
),
s,
k/SUM(
m
),
VSTACK(
i,
IFNA(
HSTACK(
x,
TRANSPOSE(
IFNA(
VSTACK(
XLOOKUP(
k,
m,
u
),
HSTACK(
s,
1-SUM(
s
)
)
),
"Other"
)
)
),
x
)
)
)
)
)
)Excel solution 5 for Top Products!, proposed by Julian Poeltl:
=LET(
D,
B3:B20,
P,
C3:C20,
Q,
D3:D20,
M,
MONTH(
D
),
C,
UNIQUE(
M&P
),
S,
MAP(
C,
LAMBDA(
A,
SUM(
FILTER(
Q,
M&P=A
)
)
)
),
REDUCE(
HSTACK(
"Month",
"Product",
"% of Month sales"
),
UNIQUE(
M
),
LAMBDA(
A,
B,
VSTACK(
A,
LET(
F,
FILTER(
HSTACK(
C,
S
),
--LEFT(
C
)=B
),
S,
SORT(
F,
2,
-1
),
T,
TAKE(
S,
2,
1
),
Su,
SUM(
TAKE(
S,
,
-1
)
),
P,
TAKE(
S,
,
-1
)/Su,
VSTACK(
HSTACK(
--LEFT(
T
),
RIGHT(
T
),
TAKE(
P,
2
)
),
HSTACK(
B,
"Other",
SUM(
DROP(
P,
2
)
)
)
)
)
)
)
)
)Excel solution 6 for Top Products!, proposed by Kris Jaganah:
=LET(
a,
MONTH(
B3:B20
),
b,
C3:C20,
c,
D3:D20,
d,
PIVOTBY(
b,
a,
c,
PERCENTOF
),
e,
INDEX(
d,
MATCH(
b,
TAKE(
d,
,
1
),
0
),
MATCH(
a,
TAKE(
d,
1
),
0
)
),
f,
SORT(
HSTACK(
a,
b,
e
),
{1,
3},
{1,
-1}
),
g,
IF(
SEQUENCE(
ROWS(
a
)
)-XMATCH(
a,
a
)>1,
"Other",
CHOOSECOLS(
f,
2
)
),
h,
GROUPBY(
HSTACK(
TAKE(
f,
,
1
),
g
),
TAKE(
f,
,
-1
),
SUM,
0,
0
),
h
)Excel solution 7 for Top Products!, proposed by John Jairo Vergara Domínguez:
=LET(
m,
MONTH(
B3:B20
),
v,
VSTACK,
h,
HSTACK,
REDUCE(
I2:K2,
UNIQUE(
m
),
LAMBDA(
a,
x,
LET(
g,
GROUPBY(
C3:C20,
D3:D20,
SUM,
,
0,
-2,
m=x
),
i,
TAKE(
IFERROR(
g/SUM(
g
),
g
),
2
),
v(
a,
h(
IF(
{1;1;1},
x
),
v(
i,
h(
"Other",
1-SUM(
i
)
)
)
)
)
)
)
)
)Excel solution 8 for Top Products!, proposed by Sunny Baggu:
=LET( _d,
MONTH(
B3:B20
), _ud,
UNIQUE(
_d
), REDUCE( {"Month",
"Product",
"% of Month sales"}, _ud, LAMBDA(
x,
y,
VSTACK(
x,
LET(
_a,
FILTER(
C3:D20,
_d = y
),
_ua,
UNIQUE(
TAKE(
_a,
,
1
)
),
_ub,
MAP(
_ua,
LAMBDA(
a,
SUM(
FILTER(
TAKE(
_a,
,
-1
),
TAKE(
_a,
,
1
) = a
)
)
)
),
_p,
ROUND(
100 * _ub / SUM(
_ub
),
0
),
_g,
SORT(
HSTACK(
_ua,
_p
),
2,
-1
),
_f,
VSTACK(
TAKE(
_g,
2
),
HSTACK(
"Other",
SUM(
DROP(
_g,
2,
1
)
)
)
),
IFNA(
HSTACK(
y,
_f
),
y
)
)
)
) ))Excel solution 9 for Top Products!, proposed by Asheesh Pahwa:
=LET(
dt,
B3:B20,
p,
C3:D20,
m,
MONTH(
dt
),
u,
UNIQUE(
m
),
r,
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
p,
m=y
),
s,
SUM(
TAKE(
f,
,
-1
)
),
t,
TAKE(
f,
,
1
),
un,
UNIQUE(
t
),
d,
DROP(
REDUCE(
"",
un,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
r,
ROUND(
SUM(
FILTER(
TAKE(
f,
,
-1
),
t=v
)
)/s*100,
0
),
HSTACK(
v,
r
)
)
)
)
),
1
),
tkt,
TAKE(
SORTBY(
d,
TAKE(
d,
,
-1
),
-1
),
2
),
IFNA(
HSTACK(
y,
VSTACK(
tkt,
HSTACK(
"Others",
100-SUM(
TAKE(
tkt,
,
-1
)
)
)
)
),
y
)
)
)
)
),
DROP(
r,
1
)
)
Excel solution 10 for Top Products!, proposed by ferhat CK:
=REDUCE(I2:K2,
SEQU