General Ledger
Active Member
- Joined
- Dec 31, 2007
- Messages
- 460
I want to have a PivotTable summarize on Salesman based on the Salesman field from related tables.
My main table is Customers. I have several transaction tables (Sales, Payments, and Discounts) related to the Customers table. My PivotTable works fine when I am summarizing by fields in the Customer table.
I am having trouble creating a PivotTable that summarizes by Salesman Name. Every table has the field Salesman Name. I do not want to sum Sales, Payments, and Discounts based on the Salesman Name from the Customer table. This is the default salesman for the customer and may not be the salesman on the actual transaction. I want to summarize based on the Salesman Name on each transaction from each table.
If I use the Salesman Name field from the Customer table as the ROW item in a PivotTable, the summing works. The values sum according to the Salesman Name from the Customers table as expected and the amounts are accurate. However, this is not what I want.
If I use the Salesman Name field from the Sales table as the ROW item, the summing of Sales is fine. However, the values for Payments and Discounts is the grand total of those tables shown on the line for each Salesman.
If I use the Salesman Name field from the Payments table as the ROW item, the summing of Payments fine. However, the values for Sales and Discounts is the grand total of those tables shown on the line for each Salesman.
Do I need a table of Salesman Name, relate it to every table, and use Salesman Name from this table as the ROW item? I think this will create an issue because all the other tables are already related. True?
I am new to PowerPivot and using Excel 2010.
Thank you.
G/L
My main table is Customers. I have several transaction tables (Sales, Payments, and Discounts) related to the Customers table. My PivotTable works fine when I am summarizing by fields in the Customer table.
I am having trouble creating a PivotTable that summarizes by Salesman Name. Every table has the field Salesman Name. I do not want to sum Sales, Payments, and Discounts based on the Salesman Name from the Customer table. This is the default salesman for the customer and may not be the salesman on the actual transaction. I want to summarize based on the Salesman Name on each transaction from each table.
If I use the Salesman Name field from the Customer table as the ROW item in a PivotTable, the summing works. The values sum according to the Salesman Name from the Customers table as expected and the amounts are accurate. However, this is not what I want.
If I use the Salesman Name field from the Sales table as the ROW item, the summing of Sales is fine. However, the values for Payments and Discounts is the grand total of those tables shown on the line for each Salesman.
If I use the Salesman Name field from the Payments table as the ROW item, the summing of Payments fine. However, the values for Sales and Discounts is the grand total of those tables shown on the line for each Salesman.
Do I need a table of Salesman Name, relate it to every table, and use Salesman Name from this table as the ROW item? I think this will create an issue because all the other tables are already related. True?
I am new to PowerPivot and using Excel 2010.
Thank you.
G/L
