List the top 3 salespersons for every quarter on the basis of total sales in that quarter.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 619
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top 3 Salespersons per Quarter with Power Query
Power Query solution 1 for Top 3 Salespersons per Quarter, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Name"}, "A", "V"),
C = Table.TransformColumns(
B,
{"A", each "Q" & Text.From(Date.QuarterOfYear(Date.From(_ & "2024")))}
),
D = Table.Group(C, {"Name", "A"}, {"Sum", each List.Sum([V])}),
E = Table.Group(
D,
{"A"},
{
"All",
each [
a = Table.AddRankColumn(_, "Rk", {"Sum", 1}, [RankKind = 1]),
b = Table.SelectRows(a, (x) => x[Rk] <= 3)
][b][Name]
}
),
F = Table.FromColumns(E[All], E[A])
in
FPower Query solution 2 for Top 3 Salespersons per Quarter, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "M", "S"),
Quarter = Table.TransformColumns(
Unpivot,
{"M", each "Q" & Text.From(Date.QuarterOfYear(Date.From(_ & "1")))}
),
Group1 = Table.Group(Quarter, {"Name", "M"}, {"S", each List.Sum([S])}),
Group2 = Table.Group(Group1, {"M", "S"}, {"A", each [Name]}),
Group3 = Table.Group(Group2, "M", {"A", each List.Combine(Table.MaxN(_, "S", 3)[A])}),
Return = Table.FromColumns(Group3[A], Group3[M])
in
ReturnPower Query solution 3 for Top 3 Salespersons per Quarter, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Col = Table.AddColumn(
Source,
"A",
each
let
a = _,
b = List.Transform(List.Split(List.Skip(Record.ToList(a)), 3), List.Sum),
c = Table.FromColumns(
{List.Transform({1 .. 4}, each "Q" & Text.From(_)), b},
{"Col1", "Col2"}
)
in
c
)[[Name], [A]],
Exp = Table.ExpandTableColumn(Col, "A", Table.ColumnNames(Col[A]{0})),
Grp = Table.Group(
Exp,
{"Col1"},
{
{
"B",
each
let
a = _,
b = List.FirstN(List.Distinct(List.Sort([Col2], 1)), 3),
c = Table.Sort(Table.SelectRows(a, each List.Contains(b, [Col2])), {"Col2", 1})[Name]
in
c
}
}
),
Sol = Table.FromColumns(Grp[B], Grp[Col1])
in
SolPower Query solution 4 for Top 3 Salespersons per Quarter, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Period", "Value"),
Transform1 = Table.TransformColumns(
Unpivot,
{"Period", each "Q" & Text.From(Date.QuarterOfYear(Date.From("1" & _)))}
),
Pivot = Table.Pivot(Transform1, List.Distinct(Transform1[Period]), "Period", "Value", List.Sum),
Columnns = List.Skip(Table.ColumnNames(Pivot)),
Transform2 = List.Transform(
Columnns,
(v) =>
[
a = List.Sort(Table.Column(Pivot, v), 1),
b = List.Last(List.FirstN(List.Distinct(a), 3)),
c = Table.SelectRows(Pivot, (x) => Record.Field(x, v) >= b),
d = Table.Sort(c, {{v, 1}, each List.PositionOf(Source[Name], [Name])})[Name]
][d]
),
Result = Table.FromColumns(Transform2, Columnns)
in
ResultPower Query solution 5 for Top 3 Salespersons per Quarter, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Q",
each List.Transform(List.Split(List.Skip(Record.ToList(_)), 3), each List.Sum(_))
)[[Name], [Q]],
Cols = List.Transform(
List.Zip(AddCol[Q]),
each [
M = List.MaxN(List.Distinct(_), 3),
P = List.Transform(M, (x) => List.PositionOf(_, x, 2)),
I = List.Combine(P),
R = List.Transform(I, each AddCol[Name]{_})
][R]
),
ColNames = List.Transform({1 .. List.Count(Cols)}, each "Q" & Text.From(_)),
Res = Table.FromColumns(Cols, ColNames)
in
ResPower Query solution 6 for Top 3 Salespersons per Quarter, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Month", "Sales"),
Transform = Table.TransformColumns(
Unpivot,
{{"Month", each "Q" & Text.From(Date.QuarterOfYear(Date.From(_ & "01")))}}
),
Group1 = Table.Group(Transform, {"Month", "Name"}, {{"Total Sales", each List.Sum(_[Sales])}}),
Group2 = Table.Group(
Group1,
{"Month"},
{
{
"Names",
each Table.SelectRows(
_,
(f) => f[Total Sales] >= List.Sort(List.Distinct(_[Total Sales]), 1){2}
)[Name]
}
}
),
Result = Table.FromColumns(Group2[Names], Group2[Month])
in
ResultPower Query solution 7 for Top 3 Salespersons per Quarter, proposed by Rafael González B.:
let
Source = Question_Table,
UP = Table.UnpivotOtherColumns(Source, {"Name"}, "Date", "Value"),
GetQ = Table.TransformColumns(UP, {"Date", each Text.From(Date.QuarterOfYear(Date.From("01-" & _ & "-2024"))) & "Q"}),
Names = Table.Group(GetQ, {"Date"}, {{"Details", each
let
GroupSale = Table.Group(_, {"Name"}, {{"Total Sales", each List.Sum([Value]), type number}}),
Ranking = Table.AddRankColumn(GroupSale, "Rank", {"Total Sales", 1}, [RankKind = RankKind.Dense]),
Top3 = Table.SelectRows(Ranking, each [Rank] <= 3)[Name]
in
Top3}})[Details],
Answer = Table.FromColumns(Names, {"Q1", "Q2", "Q3","Q4"})
in
Answer
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 8 for Top 3 Salespersons per Quarter, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Month", "Value"),
Date = Table.TransformColumnTypes(
Table.AddColumn(Unpivot, "Quarter", each [Month] & ", 2024"),
{{"Quarter", type date}}
),
Qtr = Table.TransformColumns(
Table.TransformColumns(Date, {{"Quarter", Date.QuarterOfYear, Int64.Type}}),
{{"Quarter", each "Q " & Text.From(_, "en-IN"), type text}}
),
Grp1 = Table.Group(Qtr, {"Quarter", "Name"}, {{"Sum", each List.Sum([Value])}}),
Sort = Table.Sort(
Grp1,
{{"Quarter", Order.Ascending}, {"Sum", Order.Descending}, {"Name", Order.Ascending}}
),
Grp2 = Table.Group(
Sort,
{"Quarter"},
{
{
"Names",
each Table.SelectRows(
Table.AddRankColumn(_, "Rank", {"Sum", Order.Descending}, [RankKind = RankKind.Dense]),
each [Rank] < 4
)[Name]
}
}
),
Transpose = Table.PromoteHeaders(Table.Transpose(Grp2)),
Custom = Table.AddColumn(
Transpose,
"Custom",
each Table.FromColumns({[Q 1], [Q 2], [Q 3], [Q 4]}, Table.ColumnNames(Transpose))
)[[Custom]],
Result = Table.ExpandTableColumn(
Custom,
"Custom",
Table.ColumnNames(Transpose),
Table.ColumnNames(Transpose)
)
in
ResultPower Query solution 9 for Top 3 Salespersons per Quarter, proposed by Alexandre Garcia:
let
H = Table.Sort(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], {"Name", 1}),
P = Table.ToList(
H,
(x) =>
let
a = List.Split(List.Skip(x), 3)
in
Table.FromRows(
List.Transform(List.Positions(a), each {"Q" & Text.From(_ + 1), {x{0}, List.Sum(a{_})}}),
{"x", "y"}
)
),
L = Table.Pivot(
Table.Combine(P),
P{0}[x],
"x",
"y",
(x) =>
List.Zip(
List.Select(
List.Sort(x, {each _{1}, 1}),
each List.Contains(List.MaxN(List.Distinct(List.Zip(x){1}), 3), _{1})
)
){0}
),
C = Table.FromColumns(Record.ToList(L{0}), Value.Type(L))
in
CSolving the challenge of Top 3 Salespersons per Quarter with Excel
Excel solution 1 for Top 3 Salespersons per Quarter, proposed by Bo Rydobon 🇹🇭:
=DROP(
REDUCE(
0,
SEQUENCE(
4
),
LAMBDA(
a,
i,
LET(
t,
MMULT(
B2:M10,
N(
ROUNDUP(
SEQUENCE(
12
)/3,
)=i
)
),
IFNA(
HSTACK(
a,
TAKE(
SORTBY(
A2:A10,
-t
),
SUM(
N(
t>LARGE(
UNIQUE(
t
),
4
)
)
)
)
),
""
)
)
)
),
,
1
)Excel solution 2 for Top 3 Salespersons per Quarter, proposed by John V.:
=IFNA(DROP(REDUCE(0,{0;3;6;9},LAMBDA(a,v,LET(i,BYROW(TAKE(DROP(B2:M10,,v),,3),SUM),HSTACK(a,TOCOL(SORTBY(IFS(i>LARGE(UNIQUE(i),4),A2:A10),-i),2))))),,1),"")Excel solution 3 for Top 3 Salespersons per Quarter, proposed by Julian Poeltl:
=LET(
N,
A2:A10,
M,
B1:M1,
Q,
B2:M10,
Mo,
ROUNDUP(
MONTH(
"1."&M
)/3,
0
),
P,
REDUCE(
0,
UNIQUE(
Mo,
1
),
LAMBDA(
A,
B,
HSTACK(
A,
BYROW(
FILTER(
Q,
Mo=B
),
LAMBDA(
A,
SUM(
A
)
)
)
)
)
),
VSTACK(
"Q"&SEQUENCE(
,
4
),
IFNA(
DROP(
REDUCE(
0,
SEQUENCE(
,
4
),
LAMBDA(
A,
B,
HSTACK(
A,
LET(
C,
CHOOSECOLS(
DROP(
P,
,
1
),
B
),
FILTER(
SORTBY(
N,
C,
-1
),
SORT(
C,
,
-1
)>LARGE(
UNIQUE(
C
),
4
)
)
)
)
)
),
,
1
),
""
)
)
)Excel solution 4 for Top 3 Salespersons per Quarter, proposed by Timothée BLIOT:
=LET(A,A2:A10,B,CEILING(SEQUENCE(,12)/3,1),C,B2:M10,F,LAMBDA(n,m,TOCOL(IFNA(n,m))),D,F(A,C),E,F(B,C),I,TOCOL(C),J,GROUPBY(HSTACK(D,E),I,SUM,,0),IFNA(DROP(REDUCE("",ROW(1:4),LAMBDA(w,v,LET(K,FILTER(J,INDEX(J,,2)=v),HSTACK(w,TAKE(SORT(FILTER(K,TAKE(K,,-1)>=LARGE(UNIQUE(TAKE(K,,-1)),3)),{3,1},{-1,1}),,1))))),,1),""))Excel solution 5 for Top 3 Salespersons per Quarter, proposed by Hussein SATOUR:
=LET(R,REDUCE,H,HSTACK,C,CHOOSECOLS,S,SEQUENCE,q,R("",{0,3,6,9},LAMBDA(x,y,H(x,BYROW(C(B2:M10,S(3)+y),SUM)))),IFNA(DROP(R(,S(5),LAMBDA(z,w,H(z,LET(a,C(q,w),TAKE(SORTBY(A2:A10,a,-1),XMATCH(LARGE(UNIQUE(a),4),SORT(a,,-1))-1))))),,1),""))Excel solution 6 for Top 3 Salespersons per Quarter, proposed by Oscar Mendez Roca Farell:
=DROP(
REDUCE(
"",
ROW(
1:4
),
LAMBDA(
i,
x,
LET(
b,
BYROW(
FILTER(
B2:M10,
MONTH(
10*COLUMN(
A:L
)
)=x
),
SUM
),
IFNA(
HSTACK(
i,
VSTACK(
"Q"&x,
TOCOL(
SORTBY(
IFS(
b>LARGE(
UNIQUE(
b
),
4
),
A2:A10
),
-b
),
2
)
)
),
""
)
)
)
),
,
1
)Excel solution 7 for Top 3 Salespersons per Quarter, proposed by Duy Tùng:
=DROP(REDUCE(0,
SEQUENCE(
4
),
LAMBDA(i,
x,
LET(a,
MMULT(OFFSET(B2,
,
(x-1)*3,
9,
3),
{1;1;1}),
IFNA(
HSTACK(
i,
VSTACK(
"Q"&x,
TOCOL(
SORTBY(
IFS(
LARGE(
UNIQUE(
a
),
3
)<=a,
A2:A10
),
-a
),
3
)
)
),
""
)))),
,
1)Excel solution 8 for Top 3 Salespersons per Quarter, proposed by Sunny Baggu:
=LET(
_m, ROUNDUP(SEQUENCE(, COLUMNS(B1:M1)) / 3, 0),
_um, UNIQUE(_m, 1),
VSTACK(
"Q" & _um,
IFNA(
& DROP(
REDUCE(
"",
_um,
LAMBDA(a, v,
HSTACK(
a,
LET(
_a, BYROW(FILTER(B2:M10, _m = v), LAMBDA(a, SUM(a))),
_b, LARGE(UNIQUE(_a), 3),
_c, SORTBY(HSTACK(A2:A10, _a), _a, -1),
FILTER(TAKE(_c, , 1), TAKE(_c, , -1) >= _b)
)
)
)
),
,
1
),
""
)
)
)Excel solution 9 for Top 3 Salespersons per Quarter, proposed by Md. Zohurul Islam:
=LET(u,
A2:A10,
v,
B1:M1,
w,
B2:M10,
qtr,
"Q" &ROUNDUP(MONTH(--(1&v))/3,
0),
unq,
UNIQUE(
qtr,
1
),
z,
IFNA(
DROP(
REDUCE(
"",
unq,
LAMBDA(
x,
y,
LET(
a,
BYROW(
FILTER(
w,
qtr=y
),
SUM
),
b,
LARGE(
UNIQUE(
a
),
SEQUENCE(
3
)
),
d,
ISNUMBER(
XMATCH(
a,
b
)
),
e,
SORT(
FILTER(
u,
d
)
),
f,
HSTACK(
x,
VSTACK(
y,
e
)
),
f
)
)
),
,
1
),
""
),
z)Excel solution 10 for Top 3 Salespersons per Quarter, proposed by Pieter de B.:
=LET(
n,
A2:A10,
d,
B2:M10,
a,
TOCOL(
IF(
TAKE(
d,
,
3
),
n
),
,
1
),
g,
GROUPBY(
a,
WRAPCOLS(
TOCOL(
d,
,
1
),
27
),
SUM,
,
0
),
DROP(
REDUCE(
"",
{1,
2,
3,
4},
LAMBDA(
x,
y,
LET(
i,
-INDEX(
g,
,
y+1
),
m,
XMATCH(
i,
SORT(
UNIQUE(
i
)
)
),
IFNA(
HSTACK(
x,
VSTACK(
"Q"&y,
DROP(
SORT(
FILTER(
HSTACK(
m,
TAKE(
g,
,
1
)
),
m<4
)
),
,
1
)
)
),
""
)
)
)
),
,
1
)
)Excel solution 11 for Top 3 Salespersons per Quarter, proposed by Hamidi Hamid:
=LET(x,DROP(TEXTSPLIT(CONCAT("/"&A2:A10&"-"&ROUNDUP(SEQUENCE(, COLUMNS(B1:M1)) / 3, 0)&"-"&B2:M10),"-","/"),1),y,GROUPBY(TAKE(x,,2),TAKE(x,,-1)*1,LAMBDA(a,SUM(a)),,0),z,PIVOTBY(TAKE(y,,1),CHOOSECOLS(y,2),TAKE(y,,-1)*1,SUM,,0,,0),w,TRANSPOSE(DROP(REDUCE(0,BYCOL(DROP(z,1,1),LAMBDA(a,BYCOL(IF(a>=LARGE(UNIQUE(a),3),a,0),ARRAYTOTEXT))),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,",")))),1)),zu,DROP(z,1,-4),r,IF(w*1>0,zu,""),t,TRANSPOSE(IFERROR(DROP(TEXTSPLIT(CONCAT("/"&BYCOL(r,LAMBDA(a,ARRAYTOTEXT(FILTER(a,a<>""))))),",","/"),1),"")),t)Excel solution 12 for Top 3 Salespersons per Quarter, proposed by Asheesh Pahwa:
=LET(
q,
"Q"&ROUNDUP(
SEQUENCE(
,
12
)/3,
0
),
u,
UNIQUE(
q,
1
),
d,
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
HSTACK(
x,
LET(
f,
FILTER(
B2:M10,
q=y
),
b,
BYROW(
f,
LAMBDA(
x,
SUM(
x
)
)
),
_u,
UNIQUE(
b
),
l,
LARGE(
_u,
SEQUENCE(
3
)
),
REDUCE(
y,
l,
LAMBDA(
a,
v,
VSTACK(
a,
FILTER(
A2:A10,
b=v
)
)
)
)
)
)
)
),
,
1
),
IFNA(
d,
""
)
)Excel solution 13 for Top 3 Salespersons per Quarter, proposed by Peter Bartholomew:
= MAPλ(
quarter,
LAMBDA(q,
LET(
qtrSales,
BYROW(TAKE(DROP(sales,
,
3 * (q - 1)),
,
3),
SUM),
sortedNm,
SORTBY(
name,
qtrSales,
-1
),
countReq,
XMATCH(
LARGE(
qtrSales,
3
),
SORT(
qtrSales,
,
-1
),
,
-1
),
TAKE(
sortedNm,
countReq
)
)
)
)Solving the challenge of Top 3 Salespersons per Quarter with Python
Python solution 1 for Top 3 Salespersons per Quarter, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "619 Top 3.xlsx"
input = pd.read_excel(path, usecols="A:M", nrows=10)
test = pd.read_excel(path, usecols="A:D", skiprows=12, nrows=6)
input = input.melt(id_vars=input.columns[0], var_name="month", value_name="value")
input['month'] = input['month'].apply(lambda x: pd.to_datetime(x, format='%b').month)
input['quarter'] = input['month'].apply(lambda x: f"Q{((x-1)//3)+1}")
result = input.groupby(['quarter', input.columns[0]]).agg({'value': 'sum'}).reset_index()
result['rank'] = result.groupby('quarter')['value'].rank(method='dense', ascending=False)
result = result[result['rank'] <= 3].sort_values(['quarter', 'rank'])
result['rn'] = result.groupby('quarter').cumcount() + 1
result = result.pivot(index='rn', columns='quarter', values=input.columns[0]).reset_index(drop=True)
result.columns.name = None
print(all(result==test)) # True
Solving the challenge of Top 3 Salespersons per Quarter with Python in Excel
Python in Excel solution 1 for Top 3 Salespersons per Quarter, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:M10", headers=True)
data = data.melt("Name", None, "M", "S")
data["M"] = pd.to_datetime(data["M"], format="%b").dt.quarter
group1 = (
data.groupby(["Name", "M"], as_index=False)
.sum()
.sort_values(["M", "S"], ascending=[True, False])
)
group1["R"] = group1.groupby("M")["S"].rank("dense", ascending=False)
top3 = group1[group1["R"] <= 3].groupby("M")["Name"].agg(list)
result = top3.apply(pd.Series).T.fillna("")
result.columns = [f"Q{i+1}" for i in range(result.shape[1])]
result
Solving the challenge of Top 3 Salespersons per Quarter with R
R solution 1 for Top 3 Salespersons per Quarter, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/619 Top 3.xlsx"
input = read_excel(path, range = "A1:M10")
test = read_excel(path, range = "A13:D18")
result = input %>%
pivot_longer(-c(1), names_to = "month", values_to = "value") %>%
mutate(month = match(month, month.abb),
quarter = paste0("Q",ceiling(month / 3))) %>%
summarise(value = sum(value), .by = c(quarter, Name)) %>%
mutate(rank = dense_rank(desc(value)), .by = quarter) %>%
filter(rank <= 3) %>%
arrange(quarter, rank) %>%
mutate(rn = row_number(), .by = quarter) %>%
select(-c(rank, value)) %>%
pivot_wider(names_from = quarter, values_from = Name) %>%
select(-rn)
all.equal(result, test)
#> [1] TRUE
&&