CODEWITHSUNDEEP
excelpivot-table
Edward
Edward

Reputation: 896

Using table with pivot to apply multiple rep postback based on client

I am trying to find an easy way using two excel tables with a pivot table on the first table to apply a commission percentage, where a client could pay out commission to multiple Reps. New data would be copy/pasted into the first table columns A:C

Not sure best way to share tables here. Google shares require a google login.

Table:

Load# ClientName Profit salesName Sales% commission
1 c1 50 =XLOOKUP([@ClientName],Table1[ClientName],Table1[salesName],0,0) =XLOOKUP([@ClientName],Table1[ClientName],Table1[%],0,0) =[@Profit]*[@[Sales%]]/100
2 c2 60 =XLOOKUP([@ClientName],Table1[ClientName],Table1[salesName],0,0) =XLOOKUP([@ClientName],Table1[ClientName],Table1[%],0,0) =[@Profit]*[@[Sales%]]/100
3 c3 70 =XLOOKUP([@ClientName],Table1[ClientName],Table1[salesName],0,0) =XLOOKUP([@ClientName],Table1[ClientName],Table1[%],0,0) =[@Profit]*[@[Sales%]]/100

Table1:

salesName ClientName %
A c1 10
B c2 10
A c3 7
B c3 8

PivotTable, on above:

sales Name Sum of commission
A 9.9
B 6

Results desired:

sales Name Sum of commission
A 9.9
B 11.6

Upvotes: 0

Views: 52

Answers (1)

Michal
Michal

Reputation: 6064

You can use Power Pivot formulas:

Set up your inputs as tables:

  1. Main Table (let's call it SalesData):
    • Columns: Load#, ClientName, Profit
  2. Lookup Table (let's call it CommissionRates):
    • Columns: salesName, ClientName, %

Load Data into Power Pivot

Create Relationships, in Power Pivot, go to the Diagram View, drag ClientName from SalesData to ClientName in CommissionRates to create a relationship.

Create Calculated Columns:

  • In the SalesData table, create a new calculated column for salesName: 
    =RELATED(CommissionRates[salesName])
  • Create another calculated column for Sales%: 
    =RELATED(CommissionRates[%])
  • Create a calculated column for commission: 
    =[Profit] * [Sales%] / 100

Insert a PivotTable using the newly created Data Model.

Upvotes: 0

Related Questions