Transpose the problem table into result table and calculate the drop in sales amount from one quarter to previous quarter. Rank them on the basis of total drop for a person (total drop is sum of column I for a person).
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 239
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Rank Sales Drop Per Person with Power Query
Power Query solution 1 for Rank Sales Drop Per Person, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
H = Table.ColumnNames(Source),
P = Table.TransformRows(
Source,
each
let
d = {[Q2] - [Q1], [Q3] - [Q2], [Q4] - [Q3]}
in
{[Name], List.Sum(d)} & d
),
S = Table.FromRows(
List.TransformMany(
P,
each List.Zip({{2, 3, 4}, List.LastN(_, 3)}),
(i, _) =>
let
f = Byte.From(_{0} = 2)
in
{
{null, i{0}}{f},
H{_{0}} & "-" & H{_{0} - 1},
_{1},
{null, List.PositionOf(List.Sort(List.Distinct(List.Zip(P){1}), 1), i{1}) + 1}{f}
}
),
{"Name", "QtQ Drop", "Amount", "Rank"}
)
in
SPower Query solution 2 for Rank Sales Drop Per Person, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Name"}, "A", "V"),
C = Table.Combine(
Table.Group(
B,
{"Name"},
{
"All",
each
let
a = List.Positions([V]),
b = List.Skip(List.Transform(a, (x) => [V]{x} - [V]{x - 1})),
c = List.Skip(List.Transform(a, (y) => [A]{y} & "-" & [A]{y - 1})),
d = Table.FromList(
List.Positions(c),
(z) => {[Name]{0}, c{z}, b{z}, List.Sum(b)},
{"Name", "QtQ Drop", "Amount", "Sum"}
)
in
d
}
)[All]
),
D = Table.AddColumn(
C,
"Rank",
each Table.RowCount(A) - List.PositionOf(List.Distinct(C[Sum]), [Sum]) - 1
),
E = Table.TransformRows(
D,
(v) =>
let
p =
if v[QtQ Drop] <> "Q2-Q1" then
Record.TransformFields(v, {{"Name", each null}, {"Rank", each null}})
else
v,
q = Record.RemoveFields(p, {"Sum"})
in
q
),
F = Table.FromRecords(E)
in
FPower Query solution 3 for Rank Sales Drop Per Person, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "A", "V"),
Fx = (x) => List.Transform({1 .. List.Count(x[V]) - 1}, each x[V]{_} - x[V]{_ - 1}),
Fy = each List.Count(List.Select(_, (x) => x < 0)),
Group = Table.Group(
Unpivot,
{"Name"},
{
{"C", each Fy(Fx(_))},
{
"B",
each
let
a = _,
b = Fx(a),
c = List.Zip({List.Skip(a[A]), List.RemoveLastN(a[A])}),
d = List.Transform(c, each Text.Combine(_, "-")),
e = Fy(b),
f = Table.FromRows(
List.Zip({{a[Name]{0}}, d, b, {e}}),
{"Name", "QtQ Drop", "Amount", "Rank"}
)
in
f
}
}
),
Sol = Table.Combine(Table.Sort(Group, {{"C", 1}})[B])
in
SolPower Query solution 4 for Rank Sales Drop Per Person, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
cab = List.Transform(
{0 .. 2},
each {"Name"} & List.Reverse(List.Range(List.Skip(Table.ColumnNames(Fonte)), _, 2))
),
trf = List.Transform(
{0 .. 2},
each
let
a = Table.SelectColumns(Fonte, cab{_}),
b = Table.AddColumn(
Table.DemoteHeaders(a),
"tab",
each [
QtQ Drop = Text.Combine(List.Skip(Table.ColumnNames(a)), "-"),
Amount = [Column2] - [Column3]
]
)
in
b
),
cmb = Table.Combine(trf),
exp = Table.ExpandRecordColumn(cmb, "tab", Record.FieldNames(cmb[tab]{0})),
err = Table.RemoveRowsWithErrors(exp, {"Amount"})[[Column1], [QtQ Drop], [Amount]],
grp = Table.AddRankColumn(
Table.Group(err, {"Column1"}, {{"tab", each _}, {"Queda", each List.Sum(_[Amount])}}),
"Rank",
{"Queda", 1}
),
expo = Table.ExpandTableColumn(grp, "tab", List.Skip(Table.ColumnNames(grp[tab]{0}))),
cls = Table.Sort(expo, {each List.PositionOf(Fonte[Name], [Column1])}),
rst = Table.ReplaceValue(
cls,
each [QtQ Drop],
null,
(a, b, c) => if not Text.StartsWith(b, "Q2") then c else a,
{"Column1", "Rank"}
),
rem = Table.RemoveColumns(rst, {"Queda"}),
ren = Table.RenameColumns(rem, {{"Column1", "Name"}})
in
renPower Query solution 5 for Rank Sales Drop Per Person, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol1 = Table.AddColumn(
Source,
"Data",
each [
a = List.Skip(Record.ToList(_)),
b = List.Skip(a),
c = List.RemoveLastN(a, 1),
d = List.Zip({b, c}),
e = List.Transform(d, each _{0} - _{1}),
f = [D = e, T = List.Sum(e)]
][f]
),
Expand = Table.ExpandRecordColumn(AddCol1, "Data", {"D", "T"}),
AddCol2 = Table.AddColumn(Expand, "Rank", each List.PositionOf(List.Sort(Expand[T], 1), [T]) + 1),
Transform = Table.TransformRows(
AddCol2,
each List.Zip({{[Name]}, {"Q2-Q1", "Q3-Q2", "Q4-Q3"}, [D], {[Rank]}})
),
Result = Table.FromRows(List.Combine(Transform), {"Name", "QtQ Drop", "Amount", "Rank"})
in
ResultPower Query solution 6 for Rank Sales Drop Per Person, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol1 = Table.AddColumn(
Source,
"Drop",
each [
a = List.Skip(Record.ToList(_)),
b = List.Skip(a),
c = List.RemoveLastN(a, 1),
d = List.Zip({b, c}),
e = List.Sum(List.Transform(d, each _{0} - _{1}))
][e]
),
AddCol2 = Table.AddColumn(
AddCol1,
"Rank",
each List.PositionOf(List.Sort(AddCol1[Drop], 1), [Drop]) + 1
),
Remove = Table.RemoveColumns(AddCol2, "Drop"),
Transform = Table.TransformRows(
Remove,
each [
a = List.RemoveLastN(List.Skip(Record.ToList(_)), 1),
b = List.Skip(a),
c = List.RemoveLastN(a, 1),
d = List.Zip({b, c}),
e = List.Transform(d, each _{0} - _{1}),
f = List.Zip({{[Name], null, null}, {"Q2-Q1", "Q3-Q2", "Q4-Q3"}, e, {[Rank], null, null}})
][f]
),
Result = Table.FromRows(List.Combine(Transform), {"Name", "QtQ Drop", "Amount", "Rank"})
in
ResultPower Query solution 7 for Rank Sales Drop Per Person, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData239"]}[Content],
RankVal = List.Buffer(
List.Sort(List.Distinct(Table.TransformRows(Source, each [Q4] - [Q1])), Order.Descending)
),
TransformRows = List.Accumulate(
Table.ToRows(Source),
{},
(s, r) =>
let
_SplitRow = List.Accumulate(
{1 .. 3},
{},
(s, c) =>
let
_Rec = [
#"QtQ Drop" = "Q" & Text.From(c + 1) & "-Q" & Text.From(c),
Amount = r{c + 1} - r{c}
]
in
s
& {
_Rec
& (
if (c = 1) then
[Name = r{0}, Rank = List.PositionOf(RankVal, r{4} - r{1}) + 1]
else
[]
)
}
)
in
s & _SplitRow
),
Result = Table.FromRecords(
TransformRows,
{"Name", "QtQ Drop", "Amount", "Rank"},
MissingField.UseNull
)
in
ResultPower Query solution 8 for Rank Sales Drop Per Person, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = {"Name", "QtQ Drop", "Amount", "Rank"},
AddedDrop = Table.AddColumn(Source, "Drop", each [Q4] - [Q1]),
AddedRank = Table.AddColumn(
AddedDrop,
"Rank",
each List.Count(List.Select(AddedDrop[Drop], (x) => [Drop] < x)) + 1
),
Func = (x as list) as list =>
List.Transform(
List.Zip({List.Range(x, 1, 3), List.Range(x, 2, 3)}),
each try _{1} - _{0} otherwise _{1} & "-" & _{0}
),
RowNames = Func(Table.ColumnNames(Source)),
AddedColumn = Table.AddColumn(
AddedRank,
"tbl",
each [
L = Record.ToList(_),
F = Func(L),
R = Table.FromColumns({{[Name]}, RowNames, F, {[Rank]}}, ColNames)
][R]
),
Res = Table.Combine(AddedColumn[tbl])
in
ResPower Query solution 9 for Rank Sales Drop Per Person, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.UnpivotOtherColumns(S, {"Name"}, "QT", "Amount"),
B = Table.Group(
A,
{"Name"},
{{"T", each _, type table [Name = text, QT = text, Amount = number]}}
),
F = (t) =>
let
a = Table.AddIndexColumn(t, "In", 1, 1),
b = Table.AddColumn(a, "QT Drop", each a[QT]{[In]} & "-" & [QT]),
c = Table.AddColumn(b, "TAmount", each b[Amount]{[In]} - [Amount]),
d = Table.RemoveRowsWithErrors(c),
e = Table.AddColumn(d, "Total", each if [In] = 1 then List.Sum(d[TAmount]) else null),
f = Table.SelectColumns(e, {"Name", "QT Drop", "TAmount", "Total"})
in
f,
D = Table.AddColumn(B, "F", each F([T])),
E = Table.Combine(D[F]),
G = Table.SelectRows(E, each ([Total] <> null)),
H = Table.AddRankColumn(G, "Rank", {"Total", 1}, [RankKind = RankKind.Competition]),
I = Table.NestedJoin(E, {"Name", "Total"}, H, {"Name", "Total"}, "R"),
J = Table.SelectColumns(I, {"QT Drop", "TAmount", "Total", "R"}),
K = Table.ExpandTableColumn(J, "R", {"Name", "Rank"}, {"Name", "Rank"}),
L = Table.SelectColumns(K, {"Name", "QT Drop", "TAmount", "Rank"})
in
LPower Query solution 10 for Rank Sales Drop Per Person, proposed by Peter Krkos:
let
Ad_T = Table.AddColumn(
Source,
"T",
each [
a = List.Skip(Record.FieldNames(_)),
b = List.Transform(
List.Zip({List.Skip(a), List.RemoveLastN(a)}),
(x) => {[Name]}
& {Text.Combine({x{0}, x{1}}, "-"), Record.Field(_, x{0}) - Record.Field(_, x{1})}
),
c = Table.FromRows(b)
][c],
type table
),
Ad_Sum = Table.AddColumn(Ad_T, "Sum", each List.Sum([T][Column3])),
Rank = List.Transform(
Ad_Sum[Sum],
each List.PositionOf(List.Sort(List.Distinct(Ad_Sum[Sum]), 1), _) + 1
),
Transformed = Table.Combine(
List.Transform(
List.Zip(
{List.Transform(Ad_Sum[T], Table.ToColumns), List.Transform(List.Split(Rank, 1), each {_})}
),
(x) => Table.FromColumns(List.Combine(x), {"Name", "QtQ Drop", "Amount", "Rank"})
)
),
ChangedType = Table.TransformColumnTypes(
Transformed,
{{"Name", type text}, {"QtQ Drop", type text}, {"Amount", Int64.Type}, {"Rank", Int64.Type}}
)
in
ChangedTypePower Query solution 11 for Rank Sales Drop Per Person, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform,
C = (x) =>
let
a = B(
{Table.ColumnNames(A), x},
each
let
x = List.Skip(_)
in
List.RemoveLastN(
B(List.Zip({x, List.Skip(x)}), each try _{1} - _{0} otherwise _{1} & "-" & _{0})
)
)
in
{{x{0}}} & a & {{List.Sum(a{1})}},
D = Table.Combine(
B(Table.ToList(A, C), each Table.FromColumns(_, {"Name", "QtQ Drop", "Amount", "Rank"}))
),
E = Table.TransformColumns(
D,
{
"Rank",
each
let
x = List.PositionOf(List.Sort(List.Distinct(List.RemoveNulls(D[Rank])), 1), _) + 1
in
if x = 0 then null else x
}
)
in
ESolving the challenge of Rank Sales Drop Per Person with Excel
Excel solution 1 for Rank Sales Drop Per Person, proposed by Bo Rydobon 🇹🇭:
=LET(
x,
B2:E4,
q,
B1:D1,
C,
TOCOL,
d,
DROP(
x,
,
1
)-DROP(
x,
,
-1
),
e,
BYROW(
d,
SUM
),
y,
q="Q1",
HSTACK(
C(
REPT(
A2:A4,
y
)
),
C(
IF(
TAKE(
x,
,
1
)>0,
C1:E1&"-"&q
)
),
C(
d
),
C(
IF(
y,
XMATCH(
e,
-SORT(
-e
)
),
""
)
)
)
)Excel solution 2 for Rank Sales Drop Per Person, proposed by 🇰🇷 Taeyong Shin:
=LET(n,
B2:E4,
h,
B1:E1,
d,
DROP(
n,
,
1
)-DROP(
n,
,
-1
),
s,
BYROW(
d,
SUM
),
F,
LAMBDA(
x,
TOCOL(
IFNA(
x,
d
)
)
),
q,
F(
DROP(
h,
,
1
)&"-"&DROP(
h,
,
-1
)
),
IF((RIGHT(
q
)="1")+{1,
2,
2,
1}-1,
HSTACK(
F(
A2:A4
),
q,
F(
d
),
F(
XMATCH(
s,
-SORT(
-s
)
)
)
),
""))Excel solution 3 for Rank Sales Drop Per Person, proposed by Kris Jaganah:
=LET(a,
TOCOL(
A2:A4&"-"&B2:E4&"-"&B1:E1
),
b,
TEXTSPLIT(
a,
"-"
),
c,
--TEXTSPLIT(
TEXTAFTER(
a,
"-"
),
"-"
),
d,
TEXTAFTER(
a,
"-",
2
),
e,
SEQUENCE(
ROWS(
a
)
),
f,
LAMBDA(z,
MAP(e,
b,
LAMBDA(x,
y,
TAKE(FILTER(z,
(e>x)*(b=y),
""),
1)))),
g,
IFERROR(
f(
c
)-c,
),
h,
f(
d
)&"-"&d,
i,
MAP(b,
LAMBDA(z,
-SUM((b=z)*g))),
j,
ROWS(
A2:E4
)-XMATCH(
i,
UNIQUE(
i
)
),
VSTACK(
{"Name",
"QtQ Drop",
"Amount",
"Rank"},
FILTER(
HSTACK(
IF(
d="Q1",
b,
""
),
h,
g,
IF(
d="Q1",
j,
""
)
),
d<>"Q4"
)
))Excel solution 4 for Rank Sales Drop Per Person, proposed by Julian Poeltl:
=IFNA(
LET(
R,
REDUCE(
HSTACK(
"Name",
"QtQ Drop",
"Amount"
),
SEQUENCE(
ROWS(
A2:A4
)
),
LAMBDA(
A,
B,
VSTACK(
A,
LET(
C,
CHOOSEROWS(
B2:E4,
& B
),
D,
TOCOL(
DROP(
C,
,
1
)-DROP(
C,
,
-1
)
),
HSTACK(
INDEX(
A2:A4,
B
),
TOCOL(
C1:E1&"-"&B1:D1
),
D,
SUM(
D
)
)
)
)
)
),
TR,
TAKE(
R,
,
-1
),
HSTACK(
TAKE(
R,
,
3
),
VSTACK(
"Rank",
DROP(
XMATCH(
TR,
SORT(
TOCOL(
UNIQUE(
TR
),
3
),
,
-1
)
),
1
)
)
)
),
""
)Excel solution 5 for Rank Sales Drop Per Person, proposed by Oscar Mendez Roca Farell:
=LET(
m,
C2:E4-B2:D4,
b,
BYROW(
m,
SUM
),
O,
TOCOL,
F,
LAMBDA(
i,
O(
REPT(
i,
{1,
0,
0}
)
)
),
HSTACK(
F(
A2:A4
),
O(
IFS(
1^m,
C1:E1&"-"&B1:D1
),
2
),
O(
m
),
IFERROR(
-F(
-XMATCH(
b,
LARGE(
b,
{1,
2,
3}
)
)
),
""
)
)
)Excel solution 6 for Rank Sales Drop Per Person, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
x,
TOCOL(
IFS(
C2:E4,
x
)
)
),
a,
f(
A2:A4
),
b,
f(
C2:E4-B2:D4
),
c,
GROUPBY(
a,
b,
SUM,
,
0
),
d,
REPT(
a,
XMATCH(
a,
a
)=SEQUENCE(
ROWS(
a
)
)
),
HSTACK(
d,
f(
C1:E1&"-"&B1:D1
),
b,
XLOOKUP(
d,
TAKE(
c,
,
1
),
MAP(
DROP(
c,
,
1
),
LAMBDA(
x,
SUM(
N(
DROP(
c,
,
1
)>x
)
)
)
)+1,
""
)
)
)Excel solution 7 for Rank Sales Drop Per Person, proposed by Sunny Baggu:
=LET(
_a,
C2:E4 - B2:D4,
_b,
TOCOL(
BYROW(
_a,
LAMBDA(
a,
SUM(
a
)
)
)
),
_s,
SEQUENCE(
ROWS(
_b
)
),
_c,
SORT(
_b,
,
-1
),
_r,
XMATCH(
_b,
_c
),
_n,
TOCOL(
EXPAND(
A2:A4,
,
3,
""
)
),
_q,
TOCOL(
IF(
SEQUENCE(
ROWS(
A2:A4
)
),
C1:E1 & "-" & B1:D1
)
),
_ra,
XLOOKUP(
_n,
A2:A4,
_r,
""
),
HSTACK(
_n,
_q,
TOCOL(
_a
),
_ra
)
)Excel solution 8 for Rank Sales Drop Per Person, proposed by Md. Zohurul Islam:
=LET(p,
A2:A4,
q,
B1:E1,
r,
B2:E4,
qr,
CHOOSECOLS(
REDUCE(
"",
q,
LAMBDA(
x,
y,
LET(
a,
OFFSET(
y,
0,
1
)&"-"&y,
b,
HSTACK(
x,
a
),
b
)
)
),
2,
3,
4
),
nam,
TOCOL(
IFNA(
p,
qr
)
),
qtr,
TOCOL(
IFNA(
qr,
p
)
),
data,
BYROW(r,
LAMBDA(x,
LET(a,
DROP(
x,
,
1
),
b,
DROP(
x,
,
-1
),
d,
(a-b),
ARRAYTOTEXT(
d
)))),
amt,
DROP(
REDUCE(
"",
data,
LAMBDA(
x,
y,
VSTACK(
x,
TOCOL(
--TEXTSPLIT(
y,
", "
)
)
)
)
),
1
),
rng,
HSTACK(
qtr,
amt
),
u,
MAP(
data,
LAMBDA(
x,
SUM(
--TEXTSPLIT(
x,
", "
)
)
)
),
rnk,
XMATCH(
u,
UNIQUE(
SORT(
u,
,
-1
)
)
),
v,
DROP(
REDUCE(
"",
UNIQUE(
nam
),
LAMBDA(
x,
y,
LET(
e,
FILTER(
rng,
nam=y
),
f,
IFNA(
HSTACK(
y,
e
),
""
),
g,
VSTACK(
x,
f
),
g
)
)
),
1
),
st,
XLOOKUP(
TAKE(
v,
,
1
),
p,
rnk,
""
),
w,
HSTACK(
v,
st
),
hdr,
HSTACK(
"Name",
"QtQ Drop",
"Amount",
"Rank"
),
result,
VSTACK(
hdr,
w
),
result)Excel solution 9 for Rank Sales Drop Per Person, proposed by Hamidi Hamid:
=LET(
ad,
A2:A4,
x,
TOCOL(
IFNA(
B1:E1,
ad
)
),
s,
VSTACK(
{"",
""}*1,
HSTACK(
DROP(
x,
1
),
DROP(
x,
-1
)
)
),
y,
TOCOL(
-A2:D4+B2:E4
),
g,
TOCOL(
IFNA(
ad,
B1:E1
)
),
u,
TAKE(
s,
,
1
)&"-"&CHOOSECOLS(
s,
2
),
r,
IF(
u="q2-q1",
g,
""
),
m,
HSTACK(
r,
u,
y
),
ff,
FILTER(
m,
NOT(
ISERROR(
TAKE(
m,
,
-1
)
)
)
),
xx,
SORT(
BYROW(
C2:E4-B2:D4,
SUM
),
,
-1
),
mo,
HSTACK(
ad,
SORT(
XMATCH(
xx,
xx
),
,
-1
)
),
ws,
IFERROR(
VLOOKUP(
TAKE(
ff,
,
1
),
mo,
2,
0
),
""
),
VSTACK(
{"Name",
"QtQ Drop",
"Amount",
"Rank"},
HSTACK(
ff,
ws
)
)
)Excel solution 10 for Rank Sales Drop Per Person, proposed by Asheesh Pahwa:
=LET(
r,
DROP(
REDUCE(
G1:J1,
SEQUENCE(
3
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
B2:E4,
y,
),
s,
DROP(
SEQUENCE(
COLUMNS(
I
)
)-1,
1
),
d,
DROP(
REDUCE(
"",
s,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_i,
INDEX(
I,
,
v+1
)-INDEX(
I,
,
v
),
_i2,
INDEX(
B1:E1,
,
v+1
)&"-"&INDEX(
B1:E1,
,
v
),
HSTACK(
_i2,
_i
)
)
)
)
),
1
),
n,
INDEX(
A2:A4,
y,
),
IFNA(
HSTACK(
n,
d,
SUM(
d
)
),
""
)
)
)
)
),
1
),
t,
TAKE(
r,
,
-1
),
u,
SORT(
UNIQUE(
FILTER(
t,
t<>""
)
),
,
-1
),
HSTACK(
DROP(
r,
,
-1
),
XLOOKUP(
t,
u,
SEQUENCE(
ROWS(
u
)
),
""
)
)
)Excel solution 11 for Rank Sales Drop Per Person, proposed by ferhat CK:
=LET(
a,
A2:E4,
b,
C2:E4-B2:D4,
IFNA(
REDUCE(
G1:J1,
SEQUENCE(
ROWS(
a
)
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
CHOOSEROWS(
TAKE(
a,
,
1
),
y
),
TOCOL(
C1:E1&"-"&B1:D1
),
TOCOL(
CHOOSEROWS(
b,
y
)
),
XMATCH(
SUM(
CHOOSEROWS(
b,
y
)
),
SORT(
BYROW(
b,
SUM
),
,
-1
)
)
)
)
)
),
""
)
)Excel solution 12 for Rank Sales Drop Per Person, proposed by Eddy Wijaya:
=LET(
r_gl,
SORT(
BYROW(
B2:E4,
LAMBDA(
r,
SUM(
DROP(
r-OFFSET(
r,
,
-1
),
,
1
)
)
)
),
,
-1
),
r,
REDUCE(
G1:J1,
A2:A4,
LAMBDA(
a,
v,
VSTACK(
a,
IFNA(
HSTACK(
v,
TOCOL(
C1:E1&"-"&B1:D1
),
LET(
am,
TOCOL(
OFFSET(
v,
,
2,
1,
3
)-OFFSET(
v,
,
1,
1,
3
)
),
HSTACK(
am,
XMATCH(
SUM(
am
),
r_gl,
0
)
)
)
),
""
)
)
)
),
r
)Excel solution 13 for Rank Sales Drop Per Person, proposed by Miguel Angel Franco García:
=LET(
a;
A2:A4&"-"&B1:D1&" "&C1:E1;
b;
ENCOL(
a
);
c;
C2:E4-B2:D4;
d;
ENCOL(
c
);
e;
ENCOL(
A2:C4
);
f;
SI(
ESNUMERO(
e
);
"";
e
);
APILARH(
f;
TEXTODESPUES(
b;
"-"
);
d
)
)Solving the challenge of Rank Sales Drop Per Person with Python
Python solution 1 for Rank Sales Drop Per Person, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_239.xlsx"
input = pd.read_excel(path, usecols="A:E", nrows=3)
test = pd.read_excel(path, useco&ls="G:J", nrows=9).rename(columns=lambda x: x.split('.')[0])
input['rownumber'] = input.reset_index().index + 1
input_long = input.melt(id_vars=['Name', 'rownumber'], var_name='quarter', value_name='sales')
input_long['QtQ Drop'] = input_long.groupby('Name')['quarter'].shift(-1) + "-" + input_long['quarter']
input_long['Amount'] = input_long.groupby('Name')['sales'].shift(-1) - input_long['sales']
input_long['Rank'] = input_long.groupby('Name')['Amount'].transform('sum').rank(method='dense', ascending=False)
input_long = input_long.sort_values(by=['rownumber', 'quarter', "Rank"])
input_long.loc[input_long['quarter'] != 'Q1', ['Name', 'Rank']] = None
result = input_long.dropna(subset=['Amount']).loc[:, ['Name', 'QtQ Drop', 'Amount', 'Rank']].reset_index(drop=True)
result['Amount'] = result['Amount'].astype('int64')
print(result.equals(test)) # True
Solving the challenge of Rank Sales Drop Per Person with Python in Excel
Python in Excel solution 1 for Rank Sales Drop Per Person, proposed by Alejandro Campos:
df = xl("A1:E4", headers=True)
# Calculate quarter-to-quarter drops
for i in range(1, 4):
df[f'Q{i+1}-Q{i}'] = df[f'Q{i+1}'] - df[f'Q{i}']
# Create result DataFrame
result = pd.concat([
pd.DataFrame({
'Name': [row['Name']] + [''] * 2,
'QtQ Drop': [f'Q{i+1}-Q{i}' for i in range(1, 4)],
'Amount': [row[f'Q{i+1}-Q{i}'] for i in range(1, 4)],
'Rank': None
}) for _, row in df.iterrows()
], ignore_index=True)
# Calculate and assign ranks
total_drop = df.assign(TotalDrop=df[['Q2-Q1', 'Q3-Q2', 'Q4-Q3']].sum(axis=1))
total_drop['Rank'] = total_drop['TotalDrop'].rank(ascending=False).astype(int)
result['Rank'] = result['Name'].map(dict(zip(total_drop['Name'],
total_drop['Rank']))).fillna(' ')
result
Solving the challenge of Rank Sales Drop Per Person with R
R solution 1 for Rank Sales Drop Per Person, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_239.xlsx"
input = read_excel(path, range = "A1:E4")
test = read_excel(path, range = "G1:J10")
result = input %>%
pivot_longer(cols = -c(1), names_to = "quarter", values_to = "sales") %>%
mutate(`QtQ Drop` = paste0(lead(quarter),"-",quarter),
Amount = lead(sales) - sales,
tot_amount = sum(Amount, na.rm = TRUE),
.by = Name) %>%
mutate(Rank = dense_rank(-tot_amount),
Name = ifelse(quarter == "Q1", Name, NA),
Rank = ifelse(quarter == "Q1", Rank, NA)) %>%
filter(!is.na(Amount)) %>%
select(Name, `QtQ Drop`, Amount, Rank)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&