Excel BI Challenge Workout 197

[EnterpriseDNA]

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 197 —
(Solutions in any language are also welcome for Excel Challenges)

List the qualifying player from each group on the basis of

1. Highest average in that group
2. A player should have played a minimum of 10 matches

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

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

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

Excel BI LinkedIn Post

Excel BI on LinkedIn: #excel #excelchallenge #powerquerychallenge...

[EnterpriseDNA]

LinkedIn Post by:
Henriette Hamer

Maybe I’m getting the gist of the MAP-funtion?

MAP(N3:N5;LAMBDA(x;FILTER($B$2:$B$13;($A$2:$A$13=x)*($K$2:$K$13=MAX(FILTER($K$2:$K$13;($A$2:$A$13=x)*($L$2:$L$13=TRUE)))))))

[EnterpriseDNA]

LinkedIn Post by:
Sunny Baggu

=LET(
    _tbl, SORT(HSTACK(A2:D13, D2:D13 / C2:C13), {1, 5}, {1, -1}),
    Sortbl, FILTER(_tbl, CHOOSECOLS(_tbl, 3) >= 10),
    DROP(
        REDUCE(
            "",
            UNIQUE(TAKE(_tbl, , 1)),
            LAMBDA(a, v,
                VSTACK(
                    a,
                    LET(
                        _ftbl, FILTER(Sortbl, CHOOSECOLS(Sortbl, 1) = v),
                        FILTER(_ftbl, TAKE(_ftbl, , -1) = LARGE(TAKE(_ftbl, , -1), 1))
                    )
                )
            )
        ),
        1,
        -3
    )
)

[EnterpriseDNA]

LinkedIn Post by:
Alejandro Simón

Adjunto mi query…

let
  Source = Table.SelectRows(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each [Matches] > 9),
  Sol = Table.Group(Source, {"Group"}, {{"Player", each 
    let
    a = Table.AddColumn(_, "Tot", each [Total Points]/[Matches]),
    b = Table.Max(a, "Tot")[Player]
    in b}})
in
  Sol

[EnterpriseDNA]

LinkedIn Post by:
Zoran Milokanović

Qualifying players w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = [Table.Group](http://table.group/)(Table.AddColumn(Table.SelectRows(Source, each [Matches] >= 10), "Avg", each [Total Points] / [Matches]), {"Group"}, {{"Player", each List.Last(Table.Sort(_, "Avg")[Player])}})
in
Solution

[EnterpriseDNA]

LinkedIn Post by:
Stevenson Yu

This ugly formula is by no means Dynamic, and is hardcoded to there being only 3 groups: A, B, C. I blame Excel for having issues with MAXIFS and calculated arrays!

=LET(A, A2:D13,
B, INDEX(A,0,3),
C, INDEX(A,0,4)/B*(B>9),
D, INDEX(A,0,1),
E, C=MAX(IF(D="A",C,0)),
F, C=MAX(IF(D="B",C,0)),
G, C=MAX(IF(D="C",C,0)),
H, TAKE(A,,2),
I, VSTACK(FILTER(H,E),FILTER(H,F),FILTER(H,G)),
I)

[EnterpriseDNA]

LinkedIn Post by:
محمد حلمي

=LET(c,C2:C13,r,SORTBY(A2:D13,c>9,-1,D2:D13/c,-1),l,UNIQUE(A2:A13),HSTACK(l,XLOOKUP(l,TAKE(r,,1),INDEX(r,,2))))

[EnterpriseDNA]

LinkedIn Post by:
Alejandra Horvath CPA, CGA

My PQ 😊

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Grouped = Table.Group(Source, {"Group"}, {{"Count", each 
    let
      a = Table.AddColumn( Table.SelectRows( _, each [Matches] >9 ), "Average", each [Total Points]/ [Matches])
    in
  Table.Sort(a, {{"Average", Order.Descending}} ) [Player]{0} }})
in
  Grouped

[EnterpriseDNA]

LinkedIn post by:
Surendra Reddy

=LET(a,FILTER(A2:D13,C2:C13>=10),b,UNIQUE(A2:A13),d,INDEX(a,,4)/INDEX(a,,3),e,INDEX(a,,2),f,INDEX(a,,1),HSTACK(b,MAP(b,LAMBDA(x,FILTER(e,(d)=MAX(FILTER(d,(f=x))))))))

[EnterpriseDNA]

LinkedIn post by:
Enrico Giorgi

Hi,
insert this formula in H3 and then drag down.

ENGLISH VERSION

=LET(mean,$D$2:$D$13/$C$2:$C$13,x,MAX(IF($A$2:$A$13=G3,IF($C$2:$C$13>=10,mean))),INDEX($B$2:$B$13,MATCH(G3&x,$A$2:$A$13&mean,0)))

ITALIAN VERSION

=LET(mean;$D$2:$D$13/$C$2:$C$13;x;MAX(SE($A$2:$A$13=G3;SE($C$2:$C$13>=10;mean)));INDICE($B$2:$B$13;CONFRONTA(G3&x;$A$2:$A$13&mean;0)))

[EnterpriseDNA]

LinkedIn Post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 gp = Table.Group(Fonte, {"Group"}, {{"Contagem", each 
 [
 a = Table.SelectRows(_, each [Matches] > 9),
 b = List.Max(Table.AddColumn(a, "media", each [Total Points]/[Matches])[media]),
 c = Table.SelectRows(a, each ([Total Points]/[Matches]) = b )
 ][c]
 }}),
 res = Table.ExpandTableColumn(gp, "Contagem", {"Player"})
in
 res

[EnterpriseDNA]

LinkedIn Post by:
محمد حلمي

Try #1

=LET(e,A2:A13,c,C2:C13,l,UNIQUE(e),HSTACK(l,MAP(l,LAMBDA(a,LET(v,FILTER(HSTACK(B2:D13,D2:D13/c),(e=a)*(c>10)),r,TAKE(v,,-1),XLOOKUP(MAX(r),r,TAKE(v,,1)))))))

[EnterpriseDNA]

LinkedIn Post by:
Guillermo Arroyo

Power Query

let
 Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 a = Table.AddColumn(Origen, "Averege", each [Total Points]/[Matches]),
 b = Table.SelectRows(a, each [Matches] > 9),
 c = Table.Group(b, {"Group"}, {{"Aux", each _, type table}}),
 d = Table.AddColumn(c, "Player", each Record.Field(Table.Max([Aux], "Averege"), "Player")),
 e = Table.RemoveColumns(d,{"Aux"})
in
 e

[EnterpriseDNA]

LinkedIn Post by:
Surendra Reddy

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 FilteredRows = Table.SelectRows(Source, each [Matches] >= 10),
 AddedCustom = Table.AddColumn(FilteredRows, "Points per Match", each [Total Points]/[Matches]),
 Result = Table.Group(
 AddedCustom, 
 {"Group"}, 
 {
 {"Player", each let a = Table.Sort(_, {"Points per Match", Order.Descending}) in a{0}[Player]}
 }
 )
in
 Result

[EnterpriseDNA]

LinkedIn Post by:
Surendra Reddy

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 FilteredRows = Table.SelectRows(Source, each [Matches] >= 10),
 AddedCustom = Table.AddColumn(FilteredRows, "Points per Match", each [Total Points] / [Matches], type number),
 SortedRows = Table.Buffer(Table.Sort(AddedCustom, {{"Group", Order.Ascending}, {"Points per Match", Order.Descending}})),
 GroupedRows = Table.Group(SortedRows, {"Group"}, {{"MaxPlayer", each _{0}[Player]}})
in
 GroupedRows

[EnterpriseDNA]

Linkedin post by:
Guillermo Arroyo

=LET(m,A2:D13,d,HSTACK(m,INDEX(m,,4)/INDEX(m,,3)),b,SORT(UNIQUE(INDEX(d,,1))),HSTACK(b,MAP(b,LAMBDA(a,@CHOOSECOLS(SORT(FILTER(d,(INDEX(d,,1)=a)*(INDEX(d,,3)>9),""),5,-1),2)))))

[EnterpriseDNA]

LinkedIn Post by:
Bo Rydobon

=LET(g,A2:A13,u,UNIQUE(g),m,C2:C13,HSTACK(u,XLOOKUP(u,g&m/D2:D13/(m>9),B2:B13,,1)))

[EnterpriseDNA]

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=LET(_a, A2:A13,_c, C2:C13,_u, UNIQUE(_a),_m, (D2:D13/_c)*(_c>9)*(_a=TOROW(_u)), HSTACK(_u, TOCOL(REPT(B2:B13, 1/(_m=BYCOL(_m,LAMBDA(c,MAX(c))))), 3)))

[EnterpriseDNA]

LinkedIn post by:
Julien Lacaze

=LET(datas,FILTER(A2:D13,C2:C13>=10),scores,BYROW(datas,LAMBDA(a,CHOOSECOLS(a,4)/CHOOSECOLS(a,3))),groups,UNIQUE(CHOOSECOLS(datas,1)),
sorted,SORT(HSTACK(DROP(datas,0,-2),scores),3,-1),
HSTACK(groups,XLOOKUP(UNIQUE(CHOOSECOLS(datas,1)),CHOOSECOLS(sorted,1),CHOOSECOLS(sorted,2))))

[EnterpriseDNA]

LinkedIn Post by:
Surendra Reddy

=LET(a,A2:A13,b,C2:C13,c,D2:D13,d,UNIQUE(a),e,MAP(d,LAMBDA(x,MAX(FILTER(c/b,(a=x)*(b>=10))))),HSTACK(d,MAP(e,LAMBDA(y,FILTER(B2:B13,c/b=y)))))