Today’s challenge is contributed by 🇰🇷 Taeyong Shin Calculate the sum, average, maximum value, minimum value, count, and payment amount for products purchased by each customer. The price of the product purchased by the customer does not include tax. Taxes are calculated by adding 10% to the amount paid. However, the payment amount is not subject to tax on zero-rated products.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 503
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Aggregate Customer Purchase Stats with Power Query
Power Query solution 1 for Aggregate Customer Purchase Stats, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source("Table1")),
each {
List.Zip(
List.Transform(
Text.Split(_{1}, ", "),
each
let
s = Text.Split(_, ": ")
in
{s{0}, Number.From(s{1}, "en-US")}
)
)
},
(i, _) => {
i{0},
List.Sum(_{1}),
List.Average(_{1}),
List.Max(_{1}),
List.Min(_{1}),
List.Count(_{1}),
List.Sum(
List.Transform(
List.Positions(_{0}),
(p) => (
Byte.From(List.PositionOf(Table.ToList(Source("Table2")), _{0}{p}) = - 1) * 0.1 + 1
)
* _{1}{p}
)
)
}
),
{"Customer", "SUM", "AVERAGE", "MAX", "MIN", "COUNT", "Payment"}
)
in
SPower Query solution 2 for Aggregate Customer Purchase Stats, proposed by Kris Jaganah:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Ans = Table.TransformColumns(
T1,
{
"Purchase product (Tax not included)",
each
let
b = Text.Remove(_, {"A" .. "Z", "a" .. "z", ":", ","}),
c = List.RemoveNulls(List.Transform(Text.Split(b, " "), Number.From)),
d = List.RemoveItems(
List.Transform(Text.Split(Text.Remove(_, {"0" .. "9", ","}), ":"), Text.Trim),
{""}
),
e = Table.FromColumns({c, d}, {"Val", "Prod"})
in
e
}
),
Xp = Table.ExpandTableColumn(Ans, "Purchase product (Tax not included)", {"Val", "Prod"}),
P = Table.AddColumn(
Xp,
"pay",
each if List.PositionOf(T2[#"Zero-tax product"], [Prod]) >= 0 then [Val] else [Val] * 1.1
),
Fin = Table.Group(
P,
{"Customer"},
{
{"SUM", each List.Sum([Val])},
{"AVERAGE", each List.Average([Val])},
{"MAX", each List.Max([Val])},
{"MIN", each List.Min([Val])},
{"COUNT", each Table.RowCount(_)},
{"Payment", each List.Sum([pay])}
}
)
in
FPower Query solution 3 for Aggregate Customer Purchase Stats, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ZeroTax = Excel.CurrentWorkbook(){[Name = "ZeroTax"]}[Content][#"Zero-tax product"],
Record = Table.AddColumn(
Source,
"Record",
each [
PP = [#"Purchase product (Tax not included)"],
S1 = Text.Split(PP, ", "),
S2 = List.Transform(
S1,
(f) => [
s = Text.Split(f, ": "),
P = s{0},
A = Number.From(s{1}),
TA = if List.Contains(ZeroTax, P) then A else A * 1.1
]
),
T = Table.FromRecords(S2, {"P", "A", "TA"}),
Customer = [Customer],
Sum = List.Sum(T[A]),
Average = List.Average(T[A]),
Max = List.Max(T[A]),
Min = List.Min(T[A]),
Count = List.Count(T[A]),
Payment = List.Sum(T[TA])
]
),
Return = Table.FromRecords(
Record[Record],
type table [
Customer = text,
Sum = Currency.Type,
Average = Currency.Type,
Max = Currency.Type,
Min = Currency.Type,
Count = Int64.Type,
Payment = Currency.Type
]
)
in
ReturnPower Query solution 4 for Aggregate Customer Purchase Stats, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Pros = Table.TransformColumns(
Tbl1,
{
"Purchase product (Tax not included)",
each
let
a = Text.Split(_, ", "),
b = List.Transform(a, each Text.Split(_, ": ")),
c = List.Transform(b, each Number.From(_{1})),
d = {List.Sum(c), List.Average(c), List.Max(c), List.Min(c), List.Count(c)},
e = List.Sum(
List.Transform(
b,
each
if List.Contains(Tbl2[#"Zero-tax product"], _{0}) then
Number.From(_{1})
else
Number.From(_{1}) * 1.1
)
)
in
Table.FromRows({d & {e}}, {"SUM", "AVERAGE", "MAX", "MIN", "COUNT", "Payment"})
}
),
Sol = Table.ExpandTableColumn(
Pros,
"Purchase product (Tax not included)",
Table.ColumnNames(Pros[#"Purchase product (Tax not included)"]{0})
)
in
SolPower Query solution 5 for Aggregate Customer Purchase Stats, proposed by 🇵🇪 Ned Navarrete C.:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
NT = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content][#"Zero-tax product"],
LF = {List.Sum, List.Average, List.Max, List.Min, List.Count},
C = Table.TransformColumns(
S,
{
{
"Purchase product (Tax not included)",
each [
a = List.TransformMany(
{_},
each Text.Split(_, ", "),
(x, _) => [n = Text.Split(_, ": "), m = {n{0}} & {Number.From(n{1}, "en-US")}][m]
),
b = Table.AddColumn(
Table.FromRows(a, {"A", "B"}),
"C",
each if List.Contains(NT, [A]) then 0 else [B] * 0.1
),
c = List.Accumulate({0 .. 4}, {}, (s, i) => s & {LF{i}(b[B])}),
d = Table.FromRows(
{c & {c{0} + LF{0}(b[C])}},
{"SUM", "AVERAGE", "MAX", "MIN", "COUNT", "Payment"}
)
][d]
}
}
),
Expand = Table.ExpandTableColumn(
C,
"Purchase product (Tax not included)",
{"SUM", "AVERAGE", "MAX", "MIN", "COUNT", "Payment"}
)
in
ExpandPower Query solution 6 for Aggregate Customer Purchase Stats, proposed by Yaroslav Drohomyretskyi:
let
Джерело = Excel.CurrentWorkbook(){[Name = "Purchase"]}[Content],
Split1 = Table.ExpandListColumn(
Table.TransformColumns(
Джерело,
{{"Purchase product (Tax not included)", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
),
"Purchase product (Tax not included)"
),
Split2 = Table.TransformColumnTypes(
Table.SplitColumn(
Table.ReplaceValue(
Split1,
",",
"",
Replacer.ReplaceText,
{"Purchase product (Tax not included)"}
),
"Purchase product (Tax not included)",
Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv),
{"Product", "Value"}
),
{{"Product", type text}, {"Value", type number}}
),
Tax = Table.AddColumn(
Split2,
"Tax",
each
if List.Contains(
Excel.CurrentWorkbook(){[Name = "ZeroTax"]}[Content][#"Zero-tax product"],
[Product]
)
then
0
else
[Value] * 0.1
),
Group = Table.Group(
Tax,
{"Customer"},
{
{"SUM", each List.Sum([Value])},
{"AVERAGE", each List.Average([Value])},
{"MAX", each List.Max([Value])},
{"MIN", each List.Min([Value])},
{"COUNT", each Table.RowCount(_)},
{"Payment", each List.Sum([Value]) + List.Sum([Tax])}
}
)
in
GroupPower Query solution 7 for Aggregate Customer Purchase Stats, proposed by Ahmed Ariem:
let
f=(w)=>
Table.ExpandTableColumn(
Table.TransformColumns(w,{"Purchaseproduct",(z)=>
[
a = Text.Select(z,{"0".."9",","," "}),
b= List.Transform( Text.Split(a, ", "),(x)=> Number.From (Text.Replace(x,",",""))),
c = List.Transform(
List.PositionOfAny( List.Transform(
List.RemoveMatchingItems(
Text.Split( Text.Select(z,{"a".."z"}&{"A".."Z"}&{":"," "}),":"),{" "}), Text.Trim),{"Apple","Fruit","Water","Milk","Fruit Box","Banana"},Occurrence.All),(x)=> b{x}),
d = List.RemoveMatchingItems(b,
c),
e = [ SUM = List.Sum(b),AVERAGE = Number.RoundUp( List.Average(b),0),
MAX = List.Max(b),MI= List.Min(b),COUNT= List.Count(b), Payment = List.Sum(d)+(List.Sum(d)*10/100) +List.Sum(c)
],
f = Table.FromRecords({e})
][f]}),"Purchaseproduct", {"SUM", "AVERAGE", "MAX", "MI", "COUNT", "Payment"}),
Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
RenameColumns = Table.RenameColumns(PromoteHeaders,{{"Purchase product (Tax not included)", "Purchaseproduct"}}),
final =f(RenameColumns)
in
final
-----
Sample excele file attached
https://1drv.ms/x/s!AiUZ0Ws7G26RjzGsoNChojlLX0tQ?e=37fWdg
Solving the challenge of Aggregate Customer Purchase Stats with Excel
Excel solution 1 for Aggregate Customer Purchase Stats, proposed by Bo Rydobon 🇹🇭:
=LET(z,TEXTSPLIT(CONCAT(REGEXREPLACE(B2:B9,"^|, ",", "&A2:A9&":")),":",", ",1),
VSTACK(F1:L1,DROP(GROUPBY(TAKE(z,,1),DROP(z,,2)*(1+{0,0,0,0,0,0.1}*ISNA(XMATCH(INDEX(z,,2),D2:D7))),HSTACK(SUM,AVERAGE,MAX,MIN,COUNT,SUM),,0),1)))Excel solution 2 for Aggregate Customer Purchase Stats, proposed by Bo Rydobon 🇹🇭:
=REDUCE(F1:L1,
B2:B9,
LAMBDA(a,
v,
VSTACK(a,
LET(b,
TEXTSPLIT(
v,
":",
", "
),
n,
--DROP(
b,
,
1
),
HSTACK(@+A9:v,
SUM(
n
),
AVERAGE(
n
),
MAX(
n
),
MIN(
n
),
ROWS(
n
),
SUM(n*(1+10%*ISNA(
XMATCH(
TAKE(
b,
,
1
),
D2:D7
)
))))))))Excel solution 3 for Aggregate Customer Purchase Stats, proposed by John V.:
=REDUCE(G1:L1,B2:B9,LAMBDA(a,v,LET(b,TEXTSPLIT(v,":",", "),h,HSTACK,d,--DROP(b,,1),VSTACK(a,h(BYCOL(d,h(SUM,AVERAGE,MAX,MIN,ROWS)),SUM(d+d*ISNA(XMATCH(TAKE(b,,1),D2:D7))/10))))))Excel solution 4 for Aggregate Customer Purchase Stats, proposed by محمد حلمي:
=REDUCE(G1:L1,B2:B9,LAMBDA(a,v,VSTACK(a,LET(
i,TEXTSPLIT(v,": ",", "),e,--DROP(i,,1),s,SUM(e),c,COUNT(e),
HSTACK(s,s/c,MAX(e),MIN(e),c,
s*1.1+SUM(-IFNA(VLOOKUP(D2:D7,i,2,),))*0.1)))))Excel solution 5 for Aggregate Customer Purchase Stats, proposed by 🇰🇷 Taeyong Shin:
=LET(f,
LAMBDA(
x,
TEXTSPLIT(
CONCAT(
REGEXREPLACE(
B2:B9,
".*?s?([a-zs]+):s(d{1,3}(?:,d{3})*)",
x,
,
1
)
),
,
", ",
1
)
),
v,
--f(
"$2, "
),
GROUPBY(f(
"${2:+"&A2:A9&", }"
),
HSTACK(v,
v,
v,
v,
v,
v*(XLOOKUP(
f(
"$1, "
),
D2:D7,
N(
+D2:D7
)+1,
1.1
))),
HSTACK(
SUM,
AVERAGE,
MAX,
MIN,
COUNT,
SUM
),
,
0))Excel solution 6 for Aggregate Customer Purchase Stats, proposed by 🇰🇷 Taeyong Shin:
=LET(
z,
D2:D7,
Callbackλ,
LAMBDA(fn,
LAMBDA(x,
LET(
t,
TEXTSPLIT(
@x,
":",
", "
),
p,
--DROP(
t,
,
1
),
IF(ARRAYTOTEXT(
fn
) = "λ",
fn(
p
)(TAKE(
t,
,
1
)),
fn(
p
))
)
)
),
Payλ,
LAMBDA(
x,
LAMBDA(
y,
SUM(
x * XLOOKUP(
y,
z,
N(
+z
) + 1,
1.1
)
)
)
),
funcs,
HSTACK(
SUM,
AVERAGE,
MAX,
MIN,
COUNT,
Payλ
),
function,
MAP(funcs,
LAMBDA(f,
Callbackλ(f))),
g,
DROP(
GROUPBY(
A1:A9,
B1:B9,
function,
3,
0
),
1
),
IF(
g = B1,
HSTACK(
"",
TEXTSPLIT(
ARRAYTOTEXT(
funcs
),
", "
)
),
g
)
)Excel solution 7 for Aggregate Customer Purchase Stats, proposed by Kris Jaganah:
=HSTACK(
VSTACK(
"",
A2:A9
),
REDUCE(
{"SUM",
"AVERAGE",
"MAX",
"MIN",
"COUNT",
"Payment"},
B2:B9,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
y,
":",
", "
),
b,
TAKE(
a,
,
1
),
c,
--TAKE(
a,
,
-1
),
d,
IFNA(
IF(
MATCH(
b,
D2:D7,
0
),
c
),
1.1*c
),
e,
HSTACK(
DROP(
PIVOTBY(
,
,
c,
HSTACK(
SUM,
AVERAGE,
MAX,
MIN,
COUNT
)
),
1
),
SUM(
d
)
),
e
)
)
)
)
)Excel solution 8 for Aggregate Customer Purchase Stats, proposed by Julian Poeltl:
=VSTACK(
HSTACK(
"",
G1:L1
),
HSTACK(
A2:A9,
--TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
B2:B9,
LAMBDA(
A,
LET(
T,
WRAPROWS(
TEXTSPLIT(
A,
{":",
", "}
),
2
),
& P,
--TAKE(
T,
,
-1
),
TT,
HSTACK(
T,
IF(
ISNUMBER(
XMATCH(
TAKE(
T,
,
1
),
D2:D7
)
),
1,
1.1
)*P
),
TEXTJOIN(
"§",
,
SUM(
P
),
AVERAGE(
P
),
MAX(
P
),
MIN(
P
),
ROWS(
P
),
SUM(
TAKE(
TT,
,
-1
)
)
)
)
)
)
),
"§",
"|"
)
)
)Excel solution 9 for Aggregate Customer Purchase Stats, proposed by Timothée BLIOT:
=REDUCE(
{"",
"SUM",
"AVERAGE",
"MAX",
"MIN",
"COUNT",
"Payment"},
ROW(
1:8
),
LAMBDA(
a,
v,
LET(
T,
SUBSTITUTE(
INDEX(
B2:B9,
v
),
",",
""
),
N,
--REGEXEXTRACT(
T,
"d+",
1
),
P,
REGEXEXTRACT(
T,
"[A-Za-z]+(s[A-Za-z]+)?",
1
),
VSTACK(
a,
HSTACK(
INDEX(
A2:A9,
v
),
SUM(
N
),
AVERAGE(
N
),
MAX(
N
),
MIN(
N
),
COUNT(
N
),
SUM(
MAP(
P,
LAMBDA(
x,
IF(
ISNUMBER(
XMATCH(
x,
D2:D7
)
),
1,
1.1
)
)
)*N
)
)
)
)
)
)Excel solution 10 for Aggregate Customer Purchase Stats, proposed by Sunny Baggu:
=REDUCE(
F1:L1,
SEQUENCE(
ROWS(
A2:A9
)
),
LAMBDA(x,
y,
VSTACK(
x,
LET(
e,
INDEX(
A2:B9,
y,
1
),
f,
INDEX(
A2:B9,
y,
2
),
LET(
ts,
TEXTSPLIT(
f,
":",
", "
),
a,
TAKE(
ts,
,
-1
) + 0,
b,
TAKE(
ts,
,
1
),
HSTACK(
e,
SUM(
a
),
AVERAGE(
a
),
MAX(
a
),
MIN(
a
),
COUNT(
a
),
SUM((1 + 0.1 * (ISNA(
XMATCH(
b,
D2:D7
)
))) * a)
)
)
)
)
)
)Excel solution 11 for Aggregate Customer Purchase Stats, proposed by Anshu Bantra:
=HSTACK(
A1:A9,
REDUCE(
{"SUM",
"AVERAGE",
"MAX",
"MIN",
"COUNT",
"Payment"},
B2:B9,
LAMBDA(
ini,
ro,
VSTACK(
ini,
LET(
arr_,
TEXTSPLIT(
ro,
": ",
", "
),
prod_,
INDEX(
arr_,
,
1
),
price_,
INDEX(
arr_,
,
2
) * 1,
lst_,
D2:D7,
tax_,
IF(
ISNUMBER(
XMATCH(
prod_,
lst_
)
),
0,
0.1
) *
price_,
total_,
price_ + tax_,
tbl_,
HSTACK(
prod_,
price_,
tax_,
total_
),
tbl1_,
HSTACK(
SUM(
price_
),
AVERAGE(
price_
),
MAX(
price_
),
MIN(
price_
),
COUNT(
price_
),
SUM(
total_
)
),
tbl1_
)
)
)
)
)Excel solution 12 for Aggregate Customer Purchase Stats, proposed by Hamidi Hamid:
=LET(
x,
REDUCE(
,
B1:B9,
LAMBDA(
a,
b,
IFERROR(
VSTACK(
a,
SUBSTITUTE(
TEXTSPLIT(
b,
{":",
", "},
),
",",
)
),
""
)
)
),
s,
DROP(
x,
1
),
q,
TOCOL(
s
),
j,
WRAPROWS(
q,
2,
),
z,
TAKE(
j,
,
-1
)*1,
p,
TAKE(
j,
,
1
),
cc,
IF(
XLOOKUP(
p,
D2:D7,
D2:D7,
0
)=p,
1,
1.1
),
r,
z*cc,
u,
COLUMNS(
s
)/2,
aa,
IFERROR(
WRAPROWS(
r,
u,
100
),
0
),
dd,
BYROW(
aa,
LAMBDA(
a,
SUM(
a
)
)
),
yy,
IFERROR(
WRAPROWS(
z,
u,
),
""
),
tt,
BYROW(
yy,
LAMBDA(
a,
SUM(
a
)&"-"&AVERAGE(
a
)&"-"&MAX(
a
)&"-"&MIN(
a
)&"-"&COUNT(
a
)
)
),
oo,
VSTACK(
"",
tt
),
kk,
REDUCE(
,
oo,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
{"-"},
)
)
)
),
jj,
DROP(
kk,
1
),
INT(
HSTACK(
jj,
dd
)
)
)Excel solution 13 for Aggregate Customer Purchase Stats, proposed by Asheesh Pahwa:
=LET(pp,B2:B9,d,DROP(REDUCE("",pp,LAMBDA(x,y,VSTACK(x,
LET(t,TEXTSPLIT(y,": ",", "),
tn,--TAKE(t,,-1),sm,SUM(tn),avg,AVERAGE(tn),mx,MAX(tn),
mn,MIN(tn),cnt,COUNTA(tn),xm,XMATCH(TAKE(t,,1),D2:D7),
in,N(ISNUMBER(xm)),I,SUM(IF(in,tn,tn*110%)),
HSTACK(sm,avg,mx,mn,cnt,I))))),1),HSTACK(A2:A9,d))Excel solution 14 for Aggregate Customer Purchase Stats, proposed by Bilal Mahmoud kh.:
=TEXTSPLIT(TEXTJOIN("|",,LET(P,WRAPROWS(TEXTSPLIT(TEXTJOIN(",",,MAP(A2:A9,B2:B9,LAMBDA(x,y,TEXTJOIN(",",,LET(I,REDUCE(,TRIM(TEXTSPLIT(y,{",",":"})),LAMBDA(n,m,LET(L,INDEX(n,COUNTA(n),1),IF(ISNUMBER(--L)*ISNUMBER(--m),VSTACK(DROP(n,-1),L&m),VSTACK(n,m))))),J,WRAPROWS(I,2),HSTACK(x&","&TAKE(J,,1),TAKE(J,,-1))))))),","),3),T,MAP(CHOOSECOLS(P,2),LAMBDA(x,IF(FILTER(D2:D7,D2:D7=x,0)=0,0.1,0))),S,CHOOSECOLS(P,3)+(T*CHOOSECOLS(P,3)),U,MAP(A2:A9,LAMBDA(x,LET(o,FILTER(--S,CHOOSECOLS(P,1)=x),k,FILTER(--CHOOSECOLS(P,3),CHOOSECOLS(P,1)=x),SUM(k)&"-"&AVERAGE(k)&"-"&MAX(k)&"-"&MIN(k)&"-"&COUNT(k)&"-"&SUM(o)))),U)),"-","|")Excel solution 15 for Aggregate Customer Purchase Stats, proposed by Imam Hambali:
=LET(
pp,
B2:B9,
cus,
A2:A9,
ztp,
D2:D7,
ts,
DROP(
TEXTSPLIT(
TEXTJOIN(
"",
1,
SUBSTITUTE(
", "&pp,
", ",
" |"&cus&":"
)
),
":",
"|"
),
1
),
t,
TAKE(
ts,
,
-1
)*1,
vt,
IFNA(
SIGN(
MATCH(
CHOOSECOLS(
ts,
2
),
ztp,
0
)
),
1.1
)*t,
gb,
GROUPBY(
TAKE(
ts,
,
1
),
HSTACK(
t,
t,
t,
t,
t,
vt
),
HSTACK(
SUM,
AVERAGE,
MAX,
MIN,
COUNT,
SUM
),
0,
0
),
f,
VSTACK(
{" ",
"SUM",
"AVERAGE",
"MAX",
"MIN",
"COUNT",
"PAYMENT"},
DROP(
gb,
1
)
),
f
)Excel solution 16 for Aggregate Customer Purchase Stats, proposed by Eddy Wijaya:
=VSTACK(H1:N1,
LET(
purchase,B2:B9,
zeroTax,D2:D7,
db,
DROP(REDUCE("",purchase,LAMBDA(a,v,
LET(
cust,OFFSET(v,,-1),
extract,WRAPROWS(TEXTSPLIT(v,{":",", "}),2),
convertval,IFERROR(--extract,extract),
amount,TAKE(convertval,,-1),
findZeroTax,IFERROR(IF(VLOOKUP(TAKE(convertval,,1),zeroTax,1,0)=TAKE(convertval,,1),amount),amount*110%),
IFNA(VSTACK(
a,
HSTACK(UNIQUE(cust),
SUM(amount),
AVERAGE(amount),
MAX(amount),
MIN(amount),
COUNTA(amount),
SUM(findZeroTax))),cust)))),1),db))Excel solution 17 for Aggregate Customer Purchase Stats, proposed by Edwin Tisnado:
=HSTACK(VSTACK("",A2:A9),REDUCE(G1:L1,B2:B9,LAMBDA(x,y,LET(d,TEXTSPLIT(y,":",", "),f,TAKE(d,,1),t,TOCOL(--d,3),u,SUM(IF(IFNA(XMATCH(f,D2:D7,0),0),t*0.1,0)),s,SUM(t),VSTACK(x,HSTACK(s,AVERAGE(t),MAX(t),MIN(t),COUNT(t),s*1.1-u))))))Excel solution 18 for Aggregate Customer Purchase Stats, proposed by El Badlis Mohd Marzudin:
=VSTACK(HSTACK("","SUM","AVERAGE","MAX","MIN","COUNT","Payment"),HSTACK(A2:A9,DROP(REDUCE("",B2:B9,LAMBDA(x,y, VSTACK(x,LET(a,TEXTSPLIT(y,": ",", "),b,TAKE(a,,1),c,DROP(a,,1)+0,d,IF(ISNUMBER(XMATCH(b,D2:D7)),c,c+(c*0.1)),HSTACK(SUM(c ),AVERAGE(c ),MAX(c ),MIN(c ), COUNT(c ),SUM(d)))))),1)))Excel solution 19 for Aggregate Customer Purchase Stats, proposed by Diarmuid Early:
= 30,
300)
=REDUCE(
F1:L1,
A2:A9,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
full,
TEXTSPLIT(
OFFSET(
v,
0,
1
),
": ",
", "
),
prices,
1*TAKE(
full,
,
-1
),
noTax,
ISNUMBER(
XMATCH(
TAKE(
full,
,
1
),
$D$2:$D$7
)
),
HSTACK(
v,
SUM(
prices
),
AVERAGE(
prices
),
MAX(
prices
),
MIN(
prices
),
ROWS(
prices
),
SUM(
prices*IF(
noTax,
1,
1.1
)
)
)
)
)
)
)
The logic...
Start from the headers,
and apply a function to each letter in col A that processes its row and adds it to the output (REDUCE does the repeated applying)Excel solution 20 for Aggregate Customer Purchase Stats, proposed by Amardeep Singh:
=VSTACK(F1:L1,HSTACK(A2:A9,
DROP(REDUCE("",B2:B9,
LAMBDA(a,e,VSTACK(a,
LET(zt,D2:D7,
x,TEXTSPLIT(e,": ",", "),
p,TAKE(x,,1),
v,--TAKE(x,,-1),
s,LAMBDA(a,SEQUENCE(ROWS(zt),,a,0)),
tax,XLOOKUP(p,zt,s(1),s(1.1)),
_sum,SUM(v),
_avg,AVERAGE(v),
_max,MAX(v),
_min,MIN(v),
_cnt,COUNT(v),
_pmt,SUM(tax*v),
HSTACK(_sum,_avg,_max,_min,_cnt,_pmt))))),1)))Excel solution 21 for Aggregate Customer Purchase Stats, proposed by Tamer Salem Shabib –:
=REDUCE(
F1:L1;
A2:A9;
LAMBDA(
a;
v;
VSTACK(
a;
LET(
full;
TEXTSPLIT(
OFFSET(
v;
0;
1
);
": ";
", "
);
& prices;
1*TAKE(
full;
;
-1
);
noTax;
ISNUMBER(
XMATCH(
TAKE(
full;
;
-1
);
$D$2:$D$7
)
);
HSTACK(
v;
SUM(
prices
);
AVERAGE(
prices
);
MAX(
prices
);
MIN(
prices
);
ROWS(
prices
);
SUM(
prices*IF(
noTax;
1;
1.1
)
)
)
)
)
)
)Solving the challenge of Aggregate Customer Purchase Stats with Python in Excel
Python in Excel solution 1 for Aggregate Customer Purchase Stats, proposed by Alejandro Campos:
import re
df = xl("A1:B9", headers=True)
zero_tax_products = {"Apple", "Fruit", "Water", "Milk", "Fruit Box", "Banana"}
def parse_products(products_str):
products_list = re.findall(r'([a-zA-Zs]+):s([d,]+)', products_str)
return {name.strip(): int(price.replace(",", "")) for name, price in products_list}
df['Products'] = df['Products'].apply(parse_products)
results = []
for index, row in df.iterrows():
customer = row['Customer']
products = row['Products']
total_sum = sum(products.values())
avg_price = total_sum / len(products)
max_price = max(products.values())
min_price = min(products.values())
count = len(products)
total_payment = sum(price if product in zero_tax_products else price * 1.1 for product, price in products.items())
results.append([customer, total_sum, avg_price, max_price, min_price, count, total_payment])
results_df = pd.DataFrame(results, columns=['', 'SUM', 'AVERAGE', 'MAX', 'MIN', 'COUNT', 'Payment'])
results_df
Python in Excel solution 2 for Aggregate Customer Purchase Stats, proposed by Abdallah Ally:
# Create a function to calculate payment
def calc_payment(product, price):
return price if product in dfn.values else 1.1 * price
df = xl("A1:B9", headers=True)
dfn = xl("D1:D7", headers=True)
# Perform data munging
df['ProductPrice'] = df.iloc[:, 1].map(lambda x: x.split(', '))
df = df.explode(column='ProductPrice').reset_index(drop=True)
df[['Product', 'Price']] = df['ProductPrice'].map(lambda x: x.split(': ')).tolist()
df['Price'] = df['Price'].map(lambda x: int(x.replace(',', '')))
df['Payment'] = df.apply(lambda x: calc_payment(x['Product'], x['Price']), axis=1)
df1 = df.groupby('Customer')['Price'].agg(['sum', 'mean', 'max', 'min', 'count']).reset_index()
df2 = df.groupby('Customer')['Payment'].sum().reset_index(drop=True)
df = pd.concat([df1, df2], axis=1).set_index(keys='Customer')
df.columns = df.columns.str.upper()
df = df.rename(columns = {'MEAN': 'AVERAGE', 'PAYMENT': 'Payment'})
df.index.name = None
df
Python in Excel solution 3 for Aggregate Customer Purchase Stats, proposed by Anshu Bantra:
zero_tax = list(xl("D1:D7", headers=True).values)
df = xl("A1:B9", headers=True)
def calculate_tax(row):
return 0 if row['Product'] in zero_tax else row['Amount']*0.1
df_new = pd.DataFrame(columns=['Customer', 'Product', 'Amount'])
for idx in range(len(df)):
for prod in df.iloc[idx,1].split(', '):
df_new = pd.concat([df_new, pd.DataFrame([(df.iloc[idx,0], *prod.replace(',','').split(': '))], columns=['Customer', 'Product', 'Amount'])], ignore_index=True)
df_new['Amount'] = df_new['Amount'].astype(int)
df_new['Tax'] = df_new.apply(calculate_tax, axis=1).round(0).astype(int)
df_new = df_new.groupby('Customer').agg( SUM=('Amount', 'sum'), AVERAGE=('Amount', lambda x: int(round(x.mean()))), MAX=('Amount', 'max'), MIN=('Amount', 'min'),COUNT=('Amount', 'count'),
AMOUNT=('Amount', 'sum'),TAX =('Tax', 'sum')
)
df_new['Payment'] = df_new['AMOUNT']+df_new['TAX']
df_new.drop(['AMOUNT', 'TAX'], axis=1, inplace=True)
df_new.index.name=None
df_new
Solving the challenge of Aggregate Customer Purchase Stats with R
R solution 1 for Aggregate Customer Purchase Stats, proposed by Konrad Gryczan, PhD:
Note. Value of Payment for client C is not correct in result table provided.
library(tidyverse)
library(readxl)
path = "Excel/503 Payments Calculations.xlsx"
input1 = read_excel(path, range = "A1:B9")
input2 = read_excel(path, range = "D1:D9") %>% pull()
test = read_excel(path, range = "F1:L9")
colnames(test)[1] = "Customer"
result = input1 %>%
separate_rows(`Purchase product (Tax not included)`, sep = ", ") %>%
separate(`Purchase product (Tax not included)`, into = c("Product","Amount"), sep = ": ") %>%
mutate(Amount = str_remove_all(Amount, ",") %>% as.numeric(),
amount_with_tax = ifelse(Product %in% input2, Amount, Amount * 1.1)) %>%
summarise(SUM = sum(Amount),
AVERAGE = mean(Amount),
MAX = max(Amount),
MIN = min(Amount),
COUNT = n() %>% as.numeric(),
Payment = sum(amount_with_tax),
.by = Customer)
all.equal(result, test)
# Column Payment has wrong value in test data
R solution 2 for Aggregate Customer Purchase Stats, proposed by Anil Kumar Goyal:
df <- read_excel("Excel/Excel_Challenge_503 - Payments Calculations.xlsx",
range = cell_cols("A:B"))
free_tax <- read_excel("Excel/Excel_Challenge_503 - Payments Calculations.xlsx",
range = cell_cols("D"))
df %>%
set_names(c("customer", "purchases")) %>%
separate_rows(purchases, sep = ", ") %>%
separate(purchases, into = c("prod", "amt"), sep = ": ") %>%
mutate(amt = str_remove_all(amt, fixed(",")) %>%
readr::parse_number()) %>%
summarise(across(.cols = amt,
.fns = list(SUM = ~sum(.),
AVERAGE = ~mean(.),
MAX = ~max(.),
MIN = ~min(.),
COUNT = ~n()),
.names = "{.fn}"),
Payment = sum(amt + (amt * c(10, 0)[1 + prod %in% free_tax[[1]]])),
.by = customer)
R solution 3 for Aggregate Customer Purchase Stats, proposed by Anil Kumar Goyal:
df <- read_excel("Excel/Excel_Challenge_503 - Payments Calculations.xlsx",
range = cell_cols("A:B"))
free_tax <- read_excel("Excel/Excel_Challenge_503 - Payments Calculations.xlsx",
range = cell_cols("D"))
df %>%
set_names(c("customer", "purchases")) %>%
separate_rows(purchases, sep = ", ") %>%
separate(purchases, into = c("prod", "amt"), sep = ": ") %>%
mutate(amt = str_remove_all(amt, fixed(",")) %>%
readr::parse_number()) %>%
left_join(free_tax %>%
set_names("prod") %>%
mutate(tax = 0),
by = "prod") %>%
replace_na(list(tax = 0.10)) %>%
summarise(across(.cols = amt,
.fns = list(SUM = ~sum(.),
AVERAGE = ~mean(.),
MAX = ~max(.),
MIN = ~min(.),
COUNT = ~n()),
.names = "{.fn}"),
Payment = sum(amt + (amt * tax)),
.by = customer)
&