Sumx across multiple tables

[sn64]

Hello,

    I have 2 tables that i'm having trouble creating a correct calculation for commission with.  One table contains the month, year, client number and sales amount for product 1, 2 and 3.  The second table shows the client number, the sales entity number that receives commission for that client, and which percent of sales for product 1,2 and 3 that sales entity receives commission for.  As an example sales entity 1 receives commission for 1% of client # 1's product # 1 sales, and .5% of their product # 2 sales etc.  Any help would be greatly appreciated, happy to further explain if needed.

Thank you

[sn64]

Below are examples of the tables.  One shows the clients info, their total sales by month and how much they are paying in admin fee for their service.  The next table shows the commission rates for the sales team.  The sales team gets commission on a % of the total sales and/or a % of the total admin fee.

Customer Financials
Customer ID	Customer name	Month	Total Sales	Total Admin fee
1122	Toy shop	November	$1,000,000	$5,000
1122	Toy shop	December	$900,000	$4,500
2233	Shirt store	November	$6,000,000	$6,000
2233	Shirt store	December	$3,000,000	$3,000
Commission Rates
Customer ID	Sales employee ID	Total Sales %	Admin Fee %
1122	500	1%	10%
2233	500	0%	20%
2233	510	1.50%	5%

I would like to be able to update the customer financials by adding each month to the data and be able to have the formula in place so I can filter a report by sales employee to see what their actual commission will be(Total Sales %*total sales, Total Admin Fee* Admin Fee% ) for a commission statement like below:

Sales ID	Customer	Month	Sales Commission	Admin Fee Commission
500	1122	November	$10,000	$500
500	1122	December	$9,000	$450
500	2255	November	$0	$1,200
500	2255	December	$0	$600
Total			$19,000	$2,750	$21,750

[v-zhangti]

Hi, @sn64 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sn64]

Below are examples of the tables.  One shows the clients info, their total sales by month and how much they are paying in admin fee for their service.  The next table shows the commission rates for the sales team.  The sales team gets commission on a % of the total sales and/or a % of the total admin fee.

Customer Financials
Customer ID	Customer name	Month	Total Sales	Total Admin fee
1122	Toy shop	November	$1,000,000	$5,000
1122	Toy shop	December	$900,000	$4,500
2233	Shirt store	November	$6,000,000	$6,000
2233	Shirt store	December	$3,000,000	$3,000
Commission Rates
Customer ID	Sales employee ID	Total Sales %	Admin Fee %
1122	500	1%	10%
2233	500	0%	20%
2233	510	1.50%	5%

I would like to be able to update the customer financials by adding each month to the data and be able to have the formula in place so I can filter a report by sales employee to see what their actual commission will be(Total Sales %*total sales, Total Admin Fee* Admin Fee% ) for a commission statement like below:

Sales ID	Customer	Month	Sales Commission	Admin Fee Commission
500	1122	November	$10,000	$500
500	1122	December	$9,000	$450
500	2255	November	$0	$1,200
500	2255	December	$0	$600
Total			$19,000	$2,750	$21,750