Excel Power Pivot Ignoring relationship

[nd0911]

I'm new with all the "power" family in excel, and for a test, i'm trying to join 2 super simple tables and display the results on a pivot table.

The data you can see here:

The tables and the connections between them you can see here (connection between the Product ID's)

The pivot table results:

The results I'm expecting to get

What i'm doing wrong ?

 

[alansidman]

Instead of Power Pivot, I would use Power Query

let
    Source = Table.NestedJoin(Table1, {"Procuct ID"}, Table2, {"Product ID"}, "Table2", JoinKind.RightOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Order ID"}, {"Table2.Order ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Procuct ID"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Table2.Order ID", "Product Name", "Cost"})
in
    #"Reordered Columns"

 

[nd0911]

Instead of Power Pivot, I would use Power Query

let
    Source = Table.NestedJoin(Table1, {"Procuct ID"}, Table2, {"Product ID"}, "Table2", JoinKind.RightOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Order ID"}, {"Table2.Order ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Procuct ID"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Table2.Order ID", "Product Name", "Cost"})
in
    #"Reordered Columns"

But do you know want i'm doing wrong ?

 

[anvg]

Hi.
Please, try with a measure

Amount:=CALCULATE(SUM('tbl_Product'[Cost]); 'tbl_Orders')

Regards,

 

[pjmorris]

anvg's measure is the solution. I hadn't read it properly, and so recreated your problem and then came to the same answer. It does seem counter-intuitive to me that your original scheme didn't filter the sum as you'd expect. But by adding 'tbl_orders' to the measure you do something called 'cross table filtering', which I read about in a number of posts dealing with many-to-many relationships.

HTH