excel - Using table with pivot to apply multiple rep postback based on client - Stack Overflow

admin管理员组

文章数量:1287888

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

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

• excel
• pivot-table

Share Improve this question Follow edited Feb 24 at 16:29 Edward asked Feb 24 at 16:08 EdwardEdward 89622 gold badges88 silver badges2929 bronze badges 1

• in the end just created a new table based on a filter from first table, then created a third table with first and second table combined. – Edward Commented Feb 28 at 21:34

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： excelUsing table with pivot to apply multiple rep postback based on clientStack Overflow