Excel BI Challenge Workout 039

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 39 —
(Power Query solutions are also welcome for Excel Challenges)

List the Teams and the total goals scored by them and sort them in descending order on total goals.

(Post answers in Comment. Your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/ds7YJxtA

#excel, #challenge, #advancedexcel, #excelchallenge, #excelproblem, #excelquestion, #excelsolution, #excelformulas, #excelfunctions, #exceltips, #exceltricks, #powerquerychallenge, #powerbichallenge, #powerqueryproblem, #M, #powerpivot

image
image1003×609 82.7 KB

Excel BI’s LinkedIn Post

2 
=LET(_t,TOCOL(A2:B11),
_ut,UNIQUE(_t),
_r,C2:C11,
_ar,--TOCOL(HSTACK(TEXTBEFORE(_r,"-"),TEXTAFTER(_r,"-"))),
_gs,MAP(_ut,LAMBDA(x,SUM(FILTER(_ar,_t=x)))),
_h,{"Team","Goal"},
_o,SORT(HSTACK(_ut,_gs),2,-1),
VSTACK(_h,_o))

Variable Description

_t - All teams
_ut - Unique teams list (without duplicates or triplicates)
_r - result column
_ar - All goals spliitted into rows with hyphen as delimiter
_gs - All goals scored by unique teams
_h - headers of output,
_o - Horizontal stacking of unique team and goals scored

Team Goals - Quadri Atharu.xlsx (26.0 KB)