Forum Discussion
Sumx across multiple tables
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
3 Replies
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.
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
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
