Home » Salesperson Commission Breakdown

Salesperson Commission Breakdown

Find the total commission of each sales person and sort it descending on total amount. T2 has got the sales persons involved in a deal and Sales amount. Commission of sales persons involved should be calculated on the basis of commission percentages listed in T1.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 212
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Salesperson Commission Breakdown with Power Query

Power Query solution 1 for Salesperson Commission Breakdown, proposed by Zoran Milokanović: 
let
  Source = each Table.ToRows(Excel.CurrentWorkbook(){[Name = _]}[Content]), 
  H = {"Name", "Amount"}, 
  P = List.Zip(
    List.TransformMany(
      Source("Table2"), 
      each List.Skip(List.RemoveNulls(_), 2), 
      (i, _) => {_, i{1}}
    )
  ), 
  S = Table.Sort(
    Table.FromRows(
      List.TransformMany(
        Source("Table1"), 
        each {List.Transform(List.PositionOf(P{0}, _{0}, 2), (p) => _{2} * P{1}{p})}, 
        (i, _) => {i{1}, List.Sum(_)}
      ), 
      H
    ), 
    {H{1}, 1}
  )
in
  S
Power Query solution 2 for Salesperson Commission Breakdown, proposed by Kris Jaganah: 
let
  S = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  Merge = Table.AddColumn(
    S("Table2"), 
    "Merge", 
    each Text.Combine(List.Skip(Record.ToList(_), 2), ", ")
  ), 
  Comm = Table.AddColumn(
    S("Table1"), 
    "Amount", 
    each List.Sum(Table.SelectRows(Merge, (x) => Text.Contains(x[Merge], [Code]))[Sales])
      * [Commission]
  ), 
  keep = Table.SelectColumns(Comm, {"Name", "Amount"}), 
  Sort = Table.Sort(keep, {"Amount", 1})
in
  Sort
Power Query solution 3 for Salesperson Commission Breakdown, proposed by Konrad Gryczan, PhD: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Tabela3"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Deal", type text}, 
      {"Sales", Int64.Type}, 
      {"Code1", type text}, 
      {"Code2", type text}, 
      {"Code3", type text}
    }
  ), 
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Changed Type", 
    {"Deal", "Sales"}, 
    "Atrybut", 
    "Wartość"
  ), 
  #"Merged Queries" = Table.NestedJoin(
    #"Unpivoted Columns", 
    {"Wartość"}, 
    T1, 
    {"Code"}, 
    "T1", 
    JoinKind.LeftOuter
  ), 
  #"Expanded {0}" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "T1", 
    {"Name", "Commission"}, 
    {"T1.Name", "T1.Commission"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}", {"Deal", "Atrybut", "Wartość"}), 
  #"Reordered Columns" = Table.ReorderColumns(
    #"Removed Columns", 
    {"T1.Name", "Sales", "T1.Commission"}
  ), 
  #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Amount", each [Sales] * [T1.Commission]), 
  #"Grouped Rows" = Table.Group(
    #"Added Custom", 
    {"T1.Name"}, 
    {{"Amount", each List.Sum([Amount]), type number}}
  ), 
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Amount", Order.Descending}}), 
  #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows", {{"T1.Name", "Name"}})
in
  #"Renamed Columns"
Power Query solution 4 for Salesperson Commission Breakdown, proposed by Aditya Kumar Darak 🇮🇳: 
let
  T1      = Excel.CurrentWorkbook(){[Name = "_T1"]}[Content], 
  T2      = Excel.CurrentWorkbook(){[Name = "_T2"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(T2, {"Deal", "Sales"}, "Head", "Code"), 
  Join    = Table.AddJoinColumn(T1, "Code", Unpivot, "Code", "Join"), 
  Expand  = Table.ExpandTableColumn(Join, "Join", {"Sales"}), 
  Amount  = Table.AddColumn(Expand, "Amount", each [Commission] * [Sales]), 
  Group   = Table.Group(Amount, "Name", {"Amount", each List.Sum([Amount])}), 
  Return  = Table.Sort(Group, {"Amount", 1})
in
  Return
Power Query solution 5 for Salesperson Commission Breakdown, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Tbl = Table.AddColumn(
    T2, 
    "A", 
    each 
      let
        a = List.RemoveNulls(List.Skip(Record.ToList(_), 2)), 
        b = List.Transform(a, (x) => Table.SelectRows(T1, each [Code] = x)), 
        c = Table.Combine(b)
      in
        c
  )[[A], [Sales]], 
  Exp = Table.ExpandTableColumn(Tbl, "A", Table.ColumnNames(Tbl[A]{0})), 
  Amt = Table.AddColumn(Exp, "B", each [Commission] * [Sales]), 
  Sol = Table.Sort(Table.Group(Amt, {"Name"}, {{"Amount", each List.Sum([B])}}), {"Amount", 1})
in
  Sol
Power Query solution 6 for Salesperson Commission Breakdown, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  tab = Table.AddColumn(
    Fonte, 
    "Amount", 
    each List.Product({List.Sum(Table.FindText(Tabela2, [Code])[Sales]), [Commission]})
  )[[Name], [Amount]], 
  res = Table.Sort(tab, {"Amount", 1})
in
  res
Power Query solution 7 for Salesperson Commission Breakdown, proposed by Hussein SATOUR: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddT2 = Table.AddColumn(
    Source, 
    "Amount", 
    each 
      let
        T2 = Table.UnpivotOtherColumns(
          Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
          {"Deal", "Sales"}, 
          "Attribute", 
          "Value"
        ), 
        v = [Code], 
        a = Table.SelectRows(T2, each ([Value] = v)), 
        c = [Commission]
      in
        List.Sum(List.Transform(a[Sales], (x) => x * c))
  )
in
  Table.Sort(Table.RemoveColumns(AddT2, {"Code", "Commission"}), {{"Amount", 1}})
Power Query solution 8 for Salesperson Commission Breakdown, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  UnpivOther = Table.RemoveColumns(
    Table.UnpivotOtherColumns(T2, {"Deal", "Sales"}, "A", "SPCode"), 
    "A"
  ), 
  Join = Table.Join(UnpivOther, "SPCode", Source, "Code", JoinKind.LeftOuter), 
  ComputeAMT = Table.AddColumn(Join, "Amount", each [Sales] * [Commission]), 
  GroupSSum = Table.Sort(
    Table.Group(ComputeAMT, {"Name"}, {{"Amount", each List.Sum([Amount])}}), 
    {"Amount", Order.Descending}
  )
in
  GroupSSum
Power Query solution 9 for Salesperson Commission Breakdown, proposed by Abdallah Ally: 
let
  Table1    = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Table2    = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Unpivot   = Table.UnpivotOtherColumns(Table2, {"Deal", "Sales"}, "Value", "Code"), 
  Group     = Table.Group(Unpivot, "Code", {"Sales", each List.Sum([Sales])}), 
  Merge     = Table.Join(Table1, "Code", Group, "Code"), 
  AddColumn = Table.AddColumn(Merge, "Amount", each [Commission] * [Sales]), 
  Result    = Table.Sort(AddColumn, each - [Amount])[[Name], [Amount]]
in
  Result
Power Query solution 10 for Salesperson Commission Breakdown, proposed by Eric Laforce: 
let
  Sources = Table.SelectRows(Excel.CurrentWorkbook(), each Text.StartsWith([Name], "tData212"))[
    Content
  ], 
  T1 = Table.Buffer(Sources{0}), 
  Transform = List.Transform(
    Table.ToRows(Sources{1}), 
    each 
      let
        _Sales = _{1}, 
        _Codes = List.RemoveNulls(List.Skip(_, 2)), 
        _T     = Table.SelectRows(T1, each List.Contains(_Codes, [Code]))
      in
        Table.AddColumn(_T, "Amount", each _Sales * [Commission])
  ), 
  Group = Table.Group(Table.Combine(Transform), "Name", {"Amount", each List.Sum([Amount])}), 
  Sort = Table.Sort(Group, {{"Amount", Order.Descending}})
in
  Sort
Power Query solution 11 for Salesperson Commission Breakdown, proposed by Eric Laforce: 
let
  Sources = Table.SelectRows(Excel.CurrentWorkbook(), each Text.StartsWith([Name], "tData212"))[
    Content
  ], 
  Unpivot = Table.UnpivotOtherColumns(Sources{1}, {"Deal", "Sales"}, "Attribute", "Value"), 
  Join = Table.Join(Unpivot, "Value", Sources{0}, "Code"), 
  Group = Table.Group(
    Join, 
    "Name", 
    {"Amount", each List.Sum(Table.AddColumn(_, "V", each [Sales] * [Commission])[V])}
  ), 
  Sort = Table.Sort(Group, {{"Amount", Order.Descending}})
in
  Sort
Power Query solution 12 for Salesperson Commission Breakdown, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  S1  = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content], 
  S2  = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content], 
  A   = Table.UnpivotOtherColumns(S2, {"Deal", "Sales"}, "Attribute", "Value"), 
  B   = Table.RenameColumns(A, {{"Value", "Code"}}), 
  C   = Table.SelectColumns(B, {"Deal", "Sales", "Code"}), 
  D   = Table.NestedJoin(C, {"Code"}, S1, {"Code"}, "C"), 
  E   = Table.ExpandTableColumn(D, "C", {"Name", "Commission"}, {"Name", "Commission"}), 
  F   = Table.AddColumn(E, "Am", each [Sales] * [Commission], type number), 
  Sol = Table.Group(F, {"Name"}, {{"Amount", each List.Sum([Am]), type number}})
in
  Sol
Power Query solution 13 for Salesperson Commission Breakdown, proposed by Yaroslav Drohomyretskyi: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Merge = Table.NestedJoin(Source, {"Code"}, Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="Table2"]}[Content], {"Deal", "Sales"}, "Attribute", "Code"), {"Code"}, "Sales", JoinKind.LeftOuter),
 Amount = Table.AddColumn(Merge, "Amount", each List.Sum([Sales][Sales])*[Commission]),
 Clear = Table.SelectColumns(Amount,{"Name", "Amount"}),
 Sort = Table.Sort(Clear,{{"Amount", Order.Descending}})
in
 Sort

2) let
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
 Unpivot = Table.UnpivotOtherColumns(Source, {"Deal", "Sales"}, "Attribute", "Code"),
 Merge = Table.NestedJoin(Unpivot, {"Code"}, Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Code"}, "Unpivoted Columns", JoinKind.LeftOuter),
 Expand = Table.ExpandTableColumn(Merge, "Unpivoted Columns", {"Name", "Commission"}, {"Name", "Commission"}),
 Group = Table.Group(Expand, {"Name"}, {{"Amount", each List.Sum([Sales])*List.Average([Commission]), type number}}),
 Sort = Table.Sort(Group,{{"Amount", Order.Descending}})
in
 Sort
Power Query solution 14 for Salesperson Commission Breakdown, proposed by Yaroslav Drohomyretskyi: 
let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  Result = Table.Sort(
    Table.SelectColumns(
      Table.AddColumn(
        Source("Table1"), 
        "Amount", 
        each List.Sum(
          Table.SelectRows(
            Table.UnpivotOtherColumns(Source("Table2"), {"Deal", "Sales"}, "Attribute", "Code"), 
            (row) => row[Code] = [Code]
          )[Sales]
        )
          * [Commission]
      ), 
      {"Name", "Amount"}
    ), 
    {{"Amount", Order.Descending}}
  )
in
  Result
Power Query solution 15 for Salesperson Commission Breakdown, proposed by Ahmed Ariem: 
let
 f=(w,i)=>List.Sum(List.Transform(List.PositionOf(s,w,2),(x)=>r{x}*i)),
 tbl1= Excel.CurrentWorkbook(){[Name="tblA"]}[Content],
 from= Table.TransformColumnTypes(tbl1,{{"Commission", type number}}),
 tbl2= Excel.CurrentWorkbook(){[Name="tblB"]}[Content],
 Types = Table.TransformColumnTypes(tbl2,{{"Sales", type number}}),
 Unpivot = Table.UnpivotOtherColumns(Types, {"Deal", "Sales"}, "NCode", "Code"),
 s = List.Buffer(Unpivot[Code]&{""}),
 r = List.Buffer(Unpivot[Sales]&{0}),
 to = from,
 AddCol = Table.AddColumn(to, "Amount", each f([Code],[Commission]))[[Name],[Amount]],
 Sort = Table.Sort(AddCol,{{"Amount", Order.Descending}})
in
 Sort
----
file atached
https://1drv.ms/x/s!AiUZ0Ws7G26RkCn7xaK19omgmGZ4?e=xw0ait
Power Query solution 16 for Salesperson Commission Breakdown, proposed by Luke Jarych: 
let
  T2        = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  T1        = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivoted = Table.UnpivotOtherColumns(T2, {"Deal", "Sales"}, "Attribute", "Code"), 
  Grouped   = Table.Group(Unpivoted, {"Code"}, {{"Sum", each List.Sum([Sales])}}), 
  Joined    = Table.Join(Grouped, "Code", T1, "Code"), 
  AmountCol = Table.AddColumn(Joined, "Amount", each [Commission] * [Sum])[[Name], [Amount]], 
  Sorted    = Table.Sort(AmountCol, {{"Amount", Order.Descending}})
in
  Sorted
Power Query solution 17 for Salesperson Commission Breakdown, proposed by Gertjan Davies: 
let
  Source = Problem_T1, 
  Other = Problem_T2, 
  UnpivotOther = Table.UnpivotOtherColumns(Other, {"Deal", "Sales"}, "Attribute", "Value"), 
  MergeT1T2 = Table.NestedJoin(
    UnpivotOther, 
    {"Value"}, 
    Source, 
    {"Code"}, 
    "Source", 
    JoinKind.LeftOuter
  ), 
  AddDetails = Table.ExpandTableColumn(
    MergeT1T2, 
    "Source", 
    {"Name", "Commission"}, 
    {"Name", "Commission"}
  ), 
  CommPerName = Table.AddColumn(AddDetails, "TotalCommision", each [Sales] * [Commission]), 
  Grouping = Table.Group(
    CommPerName, 
    {"Name"}, 
    {{"TotalCommsion", each List.Sum([TotalCommision]), type number}}
  ), 
  Sort = Table.Sort(Grouping, {{"TotalCommsion", Order.Descending}})
in
  Sort
Power Query solution 18 for Salesperson Commission Breakdown, proposed by Sanket Doijode: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Merged Queries" = Table.ExpandTableColumn(Table.RemoveColumns(Table.TransformColumns(
Table.NestedJoin(Source, {"Code"}, Table2, {"Value"}, "Count", JoinKind.LeftOuter), {"Count", each Table.RemoveColumns(_,"Value")}),"Code")
,"Count",{"Count"}),
 #"Sorted Rows" = Table.Sort(Table.RemoveColumns(Table.AddColumn(#"Merged Queries", "Amount", each [Commission]*[Count]),{"Commission","Count"}),{{"Amount", Order.Descending}})
in
 #"Sorted Rows"

Table2

let
 Source = Table.RemoveColumns(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],"Deal"),
 #"Unpivoted Other Columns" = Table.RemoveColumns(Table.UnpivotOtherColumns(Source, {"Sales"}, "Attribute", "Value"),"Attribute"),
 #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"Count", each List.Sum([Sales]), type number}})
in
 #"Grouped Rows"

Solving the challenge of Salesperson Commission Breakdown with Excel

Excel solution 1 for Salesperson Commission Breakdown, proposed by Bo Rydobon 🇹🇭: 
=SORT(HSTACK(B3:B7,MMULT((TOROW(C12:E17)=A3:A7)*C3:C7,TOCOL(IFNA(B12:B17,C12:E17)))),2,-1)
Excel solution 2 for Salesperson Commission Breakdown, proposed by محمد حلمي: 
=LET(I,
    MAP(A3:A7,
    C3:C7,
    LAMBDA(A,
    C,
    
SUM((C12:E17=A)*C*B12:B17))),
    SORT(
        HSTACK(
            B3:B7,
        &    I
        ),
        2,
        -1
    ))
Excel solution 3 for Salesperson Commission Breakdown, proposed by Kris Jaganah: 
=SORT(
    HSTACK(
        B3:B7,
        MAP(
            A3:A7,
            LAMBDA(
                v,
                SUM(
                    BYROW(
                        N(
                            MAP(
                                A12:E17,
                                LAMBDA(
                                    x,
                                    x=v
                                )
                            )
                        ),
                        SUM
                    )*B12:B17
                )
            )
        )*C3:C7
    ),
    2,
    -1
)
Excel solution 4 for Salesperson Commission Breakdown, proposed by Julian Poeltl: 
=LET(
    S,
    A3:A7,
    T,
    TOCOL(
        C12:E17&"|"&B12:B17
    ),
    B,
    TEXTBEFORE(
        T,
        "|"
    ),
    C,
    IFNA(
        XLOOKUP(
            B,
            S,
            C3:C7
        )*TEXTAFTER(
            T,
            "|"
        ),
        0
    ),
    VSTACK(
        HSTACK(
            "Name",
            "Amount"
        ),
        SORT(
            HSTACK(
                B3:B7,
                MAP(
                    S,
                    LAMBDA(
                        S,
                        SUM(
                            FILTER(
                                C,
                                B=S
                            )
                        )
                    )
                )
            ),
            2,
            -1
        )
    )
)
Excel solution 5 for Salesperson Commission Breakdown, proposed by Oscar Mendez Roca Farell: 
=SORT(HSTACK(B3:B7,
     MAP(A3:A7,
     LAMBDA(a,
     SUM((C12:E17=a)*B12:B17*XLOOKUP(
         C12:E17,
          A3:A7,
          C3:C7,
          0
     ))))),
     2,
     -1)
Excel solution 6 for Salesperson Commission Breakdown, proposed by Duy Tùng: 
=SORT(
    HSTACK(
        B3:B7,
        MAP(
            A3:A7,
            LAMBDA(
                x,
                SUM(
                    IF(
                        C12:E17=x,
                        B12:B17,
                        0
                    )
                )
            )
        )*C3:C7
    ),
    2,
    -1
)
Excel solution 7 for Salesperson Commission Breakdown, proposed by Sunny Baggu: 
=SORT(
 HSTACK(
 B3:B7,
    
 MAP(
 A3:A7,
    
 C3:C7,
    
 LAMBDA(a,
     b,
     SUM((C12:E17 = a) * b * B12:B17))
 )
 ),
    
 2,
    
 -1
)
Excel solution 8 for Salesperson Commission Breakdown, proposed by Md. Zohurul Islam: 
=LET(
u,
    A3:A7,
    
v,
    C3:C7,
    
nam,
    B3:B7,
    
cd,
    C12:E17,
    
sls,
    B12:B17,
    
a,
    MAP(u,
    v,
    LAMBDA(x,
    y,
    SUM((x=cd)*(y*sls)))),
    
b,
    SORT(
        HSTACK(
            nam,
            a
        ),
        2,
        -1
    ),
    
c,
    VSTACK(
        {"Name",
        "Amount"},
        b
    ),
    
c)
Excel solution 9 for Salesperson Commission Breakdown, proposed by Pieter de B.: 
=SORT(HSTACK(B3:B7,
    MAP(A3:A7,
    C3:C7,
    LAMBDA(a,
    b,
    SUM(B12:B17*b*(C12:E17=a))))),
    2,
    -1)
Excel solution 10 for Salesperson Commission Breakdown, proposed by Asheesh Pahwa: 
=LET(
    m,
    MAP(
        A3:A7,
        C3:C7,
        LAMBDA(
            x,
            y,
            SUM(
                N(
                    C12:E17=x
                )*B12:B17*y
            )
        )
    ),
    
    SORTBY(
        HSTACK(
            B3:B7,
            m
        ),
        m,
        -1
    )
)
Excel solution 11 for Salesperson Commission Breakdown, proposed by Asheesh Pahwa: 
=LET(
    r,
    DROP(
        REDUCE(
            "",
            SEQUENCE(
                ROWS(
                    A12:A17
                )
            ),
            LAMBDA(
                x,
                y,
                
                VSTACK(
                    x,
                    LET(
                        i,
                        INDEX(
                            B12:B17,
                            y,
                            
                        ),
                        IFNA(
                            HSTACK(
                                TOCOL(
                                    INDEX(
                                        C12:E17,
                                        y,
                                        
                                    ),
                                    1
                                ),
                                i
                            ),
                            i
                        )
                    )
                )
            )
        ),
        1
    ),
    t,
    TAKE(
        r,
        ,
        -1
    ),
    x,
    t*XLOOKUP(
        TAKE(
            r,
            ,
            1
        ),
        A3:A7,
        C3:C7
    ),
    
    m,
    MAP(
        A3:A7,
        LAMBDA(
            z,
            SUM(
                FILTER(
                    x,
                    TAKE(
            r,
            ,
            1
        )=z
                )
            )
        )
    ),
    
    SORTBY(
        HSTACK(
            B3:B7,
            m
        ),
        m,
        -1
    )
)
Excel solution 12 for Salesperson Commission Breakdown, proposed by Imam Hambali: 
=LET(
    
    l,
     LAMBDA(
         x,
          XLOOKUP(
              C12:E17,
              A3:A7,
              x
          )
     ),
    
    SORT(
        GROUPBY(
            TOCOL(
                l(
                    B3:B7
                ),
                3
            ),
             TOCOL(
                 l(
                     C3:C7
                 )*B12:B17,
                 3
             ),
            SUM,
            0,
            0
        ),
        2,
        -1
    )
    
)
Excel solution 13 for Salesperson Commission Breakdown, proposed by Gerson Pineda: 
=SORT(HSTACK(B3:B7,
    MAP(A3:A7,
    LAMBDA(x,
    OFFSET(
        x,
        ,
        2
    )*SUM((x=C12:E17)*B12:B17)))),
    2,
    -1)
Excel solution 14 for Salesperson Commission Breakdown, proposed by Milan Shrimali: 
=x)))),
    2,
    1),
    name,
    choosecols(
        table1,
        2
    ),
    stck,
    hstack(
        name,
        byrow(
            name,
            lambda(
                x,
                sum(
                    torow(
                        filter(
                            choosecols(
                                fnl,
                                1
                            ),
                            choosecols(
                                fnl,
                                3
                            )=x
                        )
                    )
                )
            )
        )
    ),
    per,
    BYROW(
        name,
        lambda(
            x,
            filter(
                choosecols(
                    table1,
                    3
                ),
                choosecols(
        table1,
        2
    )=x
            )
        )
    ),
    sort(
        hstack(
            name,
            BYROW(
                hstack(
                    stck,
                    per
                ),
                lambda(
                    x,
                    choosecols(
                        x,
                        2
                    )*choosecols(
                        x,
                        3
                    )
                )
            )
        ),
        2,
        0
    ))
Excel solution 15 for Salesperson Commission Breakdown, proposed by Edwin Tisnado: 
=LET(
    a,
    MAP(
        A3:A7,
        C3:C7,
        LAMBDA(
            x,
            y,
            SUM(
                IFERROR(
                    SUBSTITUTE(
                        C12:E17,
                        x,
                        y
                    )*B12:B17,
                    0
                )
            )
        )
    ),
    SORT(
        HSTACK(
            B3:B7,
            a
        ),
        2,
        -1
    )
)

Solving the challenge of Salesperson Commission Breakdown with Python

Python solution 1 for Salesperson Commission Breakdown, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "PQ_Challenge_212.xlsx"
T1 = pd.read_excel(path, skiprows = 1, nrows = 5, usecols="A:C")
T2 = pd.read_excel(path, skiprows = 10, nrows = 6, usecols="A:E")
test = pd.read_excel(path, skiprows = 1, nrows = 5, usecols="H:I")
test.columns = test.columns.str.replace(".1", "")
result = T2.copy()
result = pd.melt(result, id_vars=["Deal", "Sales"], value_vars=["Code1", "Code2", "Code3"], var_name="Code", value_name="Value")
 .dropna()
 .merge(T1, left_on="Value", right_on="Code", how="left")
 .assign(Amount = lambda x: x["Sales"] * x["Commission"])
 .groupby("Name")["Amount"].sum()
 .sort_values(ascending=False)
 .reset_index(drop=False)
print(result.equals(test)) # True
Python solution 2 for Salesperson Commission Breakdown, proposed by Luke Jarych: 
import pandas as pd
import xlwings as xw
import re
wb = xw.Book(r'PQ_Challenge_212.xlsx')
sh = wb.sheets[0]
table1 = sh.tables['Table1']
rng1 = sh.range(table1.range.address)
df1 = rng1.options(pd.DataFrame, header=True, index=False, numbers=float).value
table2 = sh.tables['Table2']
rng2 = sh.range(table2.range.address)
df2 = rng2.options(pd.DataFrame, header=True, index=False, numbers=int).value
df2_unpivoted = pd.melt(df2, id_vars=['Deal', 'Sales'],
 value_vars=['Code1', 'Code2', 'Code3'],
 var_name='Attribute', value_name='Code').dropna(subset=['Code'])
grouped = df2_unpivoted.groupby('Code').sum('Sales').reset_index()
merged = pd.merge(grouped, df1, on='Code', how='left')
merged['Amount'] = merged['Sales'] * merged['Commission']
merged = merged.sort_values(['Amount'], ascending=False).astype({'Amount': int})[['Name', 'Amount']]

Solving the challenge of Salesperson Commission Breakdown with Python in Excel

Python in Excel solution 1 for Salesperson Commission Breakdown, proposed by Alejandro Campos: 
result = (
 xl("A11:E17", headers=True)
 .set_index(["Deal", "Sales"])
 .stack()
 .reset_index(name="Value")
 .merge(xl("A2:C7", headers=True), left_on="Value", right_on="Code", how="left")
 .assign(Amount=lambda x: x["Sales"] * x["Commission"])
 .groupby("Name", as_index=False)["Amount"].sum()
 .sort_values(by="Amount", ascending=False)
).reset_index(drop=True)
result
Python in Excel solution 2 for Salesperson Commission Breakdown, proposed by Abdallah Ally: 
df1 = xl("A2:C7", headers=True)
df2 = xl("A11:E17", headers=True)
# Perform data munging
df2 = df2.melt(id_vars=df2.columns[:2], value_vars=df2.columns[2:])
df2 = df2.groupby('value')['Sales'].sum()
df = pd.merge(df1, df2, left_on='Code', right_on='value')
df['Amount'] = df['Commission'] * df['Sales']
df = df.loc[:, ['Name', 'Amount']]
df = df.sort_values(by='Amount', ascending=False, ignore_index=True)
df

Solving the challenge of Salesperson Commission Breakdown with R

R solution 1 for Salesperson Commission Breakdown, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_212.xlsx"
T1 = read_excel(path, range = "A2:C7")
T2 = read_excel(path, range = "A11:E17")
test = read_excel(path, range = "H2:I7")
input = T2 %>%
 pivot_longer(cols = -c(1, 2), values_to = "Code") %>%
 left_join(T1, by = "Code") %>%
 na.omit() %>%
 mutate(Amount = Sales * Commission) %>%
 summarise(Amount = sum(Amount), .by = "Name") %>%
 arrange(desc(Amount)) 
identical(input, test)
#> [1] TRUE

&&

Leave a Reply