Level 1 is sum of Level 2. Work out the values marked as X.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 672
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Level Hierarchy Totals with Power Query
Power Query solution 1 for Calculate Level Hierarchy Totals, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Idx = Table.AddIndexColumn(Source, "Idx", 0),
Res = Table.ReplaceValue(
Idx,
"X",
each
if [Level1] is null then
Source[Level1]{[Idx] - 1} - Source[Level2]{[Idx] - 1}
else
[Level1] - Source[Level2]{[Idx] + 1},
Replacer.ReplaceValue,
{"Level2"}
)[[Level1], [Level2]]
in
ResPower Query solution 2 for Calculate Level Hierarchy Totals, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = Table.ColumnNames(Source),
Idx = Table.AddIndexColumn(Source, "Idx", 0),
Fun = (tbl as table, ColName as text) =>
each [
A = Table.ToColumns(tbl),
B = List.PositionOf(ColNames, ColName),
C = List.Count(A{B}) / List.NonNullCount(A{B}),
D =
if A{B - 1}{[Idx]} = null then
A{B - 1}{[Idx] - C} - A{B}{[Idx] - C}
else
A{B - 1}{[Idx]} - A{B}{[Idx] + C},
E = A{B + 1}{[Idx]} + A{B + 1}{[Idx] + C / 2},
F = try D otherwise E
][F],
Res = List.Accumulate(
ColNames & ColNames,
Idx,
(a, v) => Table.Buffer(Table.ReplaceValue(a, "X", Fun(a, v), Replacer.ReplaceValue, {v}))
)[[Level1], [Level2], [Level3]]
in
ResSolving the challenge of Calculate Level Hierarchy Totals with Excel
Excel solution 1 for Calculate Level Hierarchy Totals, proposed by Bo Rydobon 🇹🇭:
=LET(
b,
B3:B10,
e,
EVEN(
ROW(
b
)
),
IF(
b>"",
LOOKUP(
e,
GROUPBY(
e,
A3:A10-N(
+b
),
SUM
)
),
b
)
)Excel solution 2 for Calculate Level Hierarchy Totals, proposed by Bo Rydobon 🇹🇭:
=LET(z,A3:C18,IF(z="","",REDUCE(z,COLUMN(z),LAMBDA(a,_,LET(i,SEQUENCE(ROWS(a)),c,COLUMNS(a),j,SEQUENCE(,c),b,IFNA(IF(a="x",IFERROR(DROP(a+INDEX(a,i+2^(c-j),j),,1),"X"),a),a),k,2^(c-j),IF((b="x")*(j>1),IFERROR(INDEX(b,FLOOR(i-1,k*2)+1,j-1)-INDEX(b,-1^ISODD((i-1)/k)*k+i,j),"X"),b))))))Excel solution 3 for Calculate Level Hierarchy Totals, proposed by John V.:
=LET(
b,
A3:A10,
i,
WRAPROWS(
B3:B10,
2
),
HSTACK(
b,
TOCOL(
IF(
i>"",
TOCOL(
b,
1
)-BYROW(
i,
SUM
),
i
)
)
)&""
)Excel solution 4 for Calculate Level Hierarchy Totals, proposed by Kris Jaganah:
=LET(
a,
A3:A18,
b,
B3:B18,
c,
C3:C18,
d,
SCAN(
0,
a<>"",
SUM
),
e,
SCAN(
0,
b<>"",
SUM
),
o,
LAMBDA(
u,
v,
w,
LET(
f,
GROUPBY(
u,
HSTACK(
v,
w
),
SUM,
,
0
),
g,
TAKE(
f,
,
1
),
h,
INDEX(
f,
,
2
),
i,
TAKE(
f,
,
-1
),
j,
h-i,
k,
IF(
j<0,
"X",
j
),
l,
IF(
w="X",
XLOOKUP(
u,
g,
k
),
w
),
l
)
),
n,
o(
e,
b,
c
),
p,
o(
d,
a,
b
),
q,
IF(
p="X",
o(
e,
c,
b
),
p
),
r,
o(
e,
q,
n
),
s,
o(
d,
q,
a
),
t,
HSTACK(
s,
q,
r
),
IF(
t=0,
"",
t
)
)Excel solution 5 for Calculate Level Hierarchy Totals, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=LET(n, WRAPROWS(TOCOL(A3:B10,,FALSE), 4), r, BYROW(n, LAMBDA(z, LET(m, MAX(z), REDUCE(0, z, LAMBDA(x, y, IF(y=m, x+y, IF(y<>"X", x-y, x))))))), s, HSTACK(r, n), xa, BYROW(s, LAMBDA(a, TEXTJOIN(",", FALSE, a))), ya, MAP(xa, LAMBDA(xx, LET(z, TEXTSPLIT(xx, ,","), zz, TAKE(z, 1), SUBSTITUTE(xx, "X", zz)))), za, TEXTJOIN(";",,BYROW(ya, LAMBDA(yy, LET(poz, FIND(",", yy), RIGHT(yy, LEN(yy) - poz))))), aa, TEXTSPLIT(za, ",",";",FALSE), WRAPROWS(TOCOL(aa),2))Excel solution 6 for Calculate Level Hierarchy Totals, proposed by Hussein SATOUR:
=TOCOL(
LET(
a,
WRAPROWS(
B3:B10,
2
),
IF(
a="X",
FILTER(
A3:A10,
A3:A10<>""
)-BYROW(
IFERROR(
a,
0
),
SUM
),
a
)
)
)Excel solution 7 for Calculate Level Hierarchy Totals, proposed by Duy Tùng:
=LET(a,SCAN(0,A3:A10>0,SUM),b,REDUCE(A2:B2,UNIQUE(a),LAMBDA(x,y,LET(u,FILTER(A3:B10,a=y),VSTACK(x,IF(u="X",MAX(u)-MIN(u),u))))),IF(b=0,"",b))Excel solution 8 for Calculate Level Hierarchy Totals, proposed by Md. Zohurul Islam:
=LET(u,
A3:A10,
d,
B3:B10,
b,
SCAN(
0,
u,
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
sq,
SEQUENCE(
COUNTA(
d
)
),
n,
FILTER(
sq,
MOD(
sq,
2
)=0
),
w,
DROP(REDUCE("",
n,
LAMBDA(x,
y,
LET(b,
DROP(TAKE(
d,
y
),
IF((y-2)<0,
0,
y-2)),
d,
SUM(UNIQUE(DROP(TAKE(
u,
y
),
IF((y-2)<0,
0,
y-2)))),
e,
IFERROR(
ABS(
b
),
0
),
f,
IF(
e>0,
e,
d-SUM(
e
)
),
g,
VSTACK(
x,
f
),
g))),
1),
VSTACK(
"Level"&SEQUENCE(
,
2
),
HSTACK(
u,
w
)
))Excel solution 9 for Calculate Level Hierarchy Totals, proposed by Hamidi Hamid:
=LET(as,A3:A10,b,B3:B10,ab,A3:B10,x,WRAPROWS(TOCOL(IF(ab="x",0,ab)),4),y,CHOOSECOLS(x,1,2,4),g,HSTACK("X"&SEQUENCE(ROWS(ab)),TAKE(y,,1)-BYROW(TAKE(y,,-2),LAMBDA(a,SUM(a)))),z,"x"&SCAN(0,IF(b="x",1,0),SUM),HSTACK(IF(as="","",as),IF(b="x",XLOOKUP(z,TAKE(g,,1),TAKE(g,,-1),0),b)))Excel solution 10 for Calculate Level Hierarchy Totals, proposed by Josh Brodrick:
=SUBSTITUTE(
LET(
a,
FILTER(
A3:A10,
A3:A10<>""
),
b,
FILTER(
B3:B10,
B3:B10<>"X"
),
HSTACK(
A3:A10,
TOCOL(
HSTACK(
b,
a-b
)
)
)
),
0,
""
)Solving the challenge of Calculate Level Hierarchy Totals with R
R solution 1 for Calculate Level Hierarchy Totals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/672 Find Level Entries.xlsx"
input = read_excel(path, range = "A3:B10", col_names = c("Level1", "Level2"))
test = read_excel(path, range = "D3:E10", col_names = c("Level1", "Level2"))
result = input %>%
mutate(rn = row_number(), Level = Level1) %>%
fill(Level1) %>%
arrange(Level1, Level2) %>%
mutate(across(everything(), as.numeric),
Level2 = ifelse(is.na(Level2), Level1 - lag(Level2), Level2)) %>%
arrange(rn) %>%
select(Level1 = Level, Level2)
all.equal(result, test, check.attributes = FALSE) # True
&&&