# Excel Power Pivot Ignoring relationship



## nd0911 (Nov 4, 2020)

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 (Nov 4, 2020)

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 (Nov 4, 2020)

alansidman said:


> Instead of Power Pivot, I would use Power Query
> 
> 
> ```
> ...



But do you know want i'm doing wrong ?


----------



## anvg (Nov 4, 2020)

Hi.
Please, try with a measure

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


----------



## pjmorris (Nov 7, 2020)

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


----------

