Pivot report based on two related tables???

[mwaltercpa]

I have two tables, one has the [Sales data] (Customer and Sales Dollars), the other table has [Order Details] (Product line and SKU). Both are related with a 1:1 key.

I want a pivot report to show Customer (from sales) in the column, SKU (from orders) in the next column, and Product line (from orders) in the next... Then I want to sum sales in the math box.

What I'm getting however is unique customer# | ALL SKUS | and ALL PRODUCT LINES | The math correctly shows total sales for the customer#, however the sales$ are reapeating on every SKU and PL row.

Is there a way to blend the qualities from both tables (connected with a unique key), and show appropriate sales totals?

 

[mwaltercpa]

Let me also clarify that the leading field on my pivot report is the unique key that both tables share... it seems like the table I pull the key from wins.. meaning the data is specific depending on which table I pull the key from... the data reported on from the other table is not specific/filtered based on the key...

 

[mwaltercpa]

Answered my own question

“Relationship may be needed?” But I already have a relationship! What’s going on? « PowerPivotPro

Nice to know you can bring in related fields from the related lookup table.