CODEWITHSUNDEEP
excelpowerpivot
user3673417
user3673417

Reputation: 189

Excel Power Pivot - Many 2 Many Relationship

I have just started to use Power Pivot and my first real world problem involves a many to many relationship. I am looking to show the quantity of each child item required each month as a consequence of the demand for the parent items and the parent / child relationships. I have created relationships as below using a unique list of items in Item Master to provide a 1 to Many relationship with the two tables.

I have created a calculated column in the Relationship table but the quantity of each item is the same each month although total qty appears correct in the Pivot Table.

=CALCULATE(sum(ItemDemand[Qty]),Relationship[Child Item])`

Any ideas gratefully received

Thanks

Upvotes: 0

Views: 601

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40214

I couldn't quite reproduce your incorrect table, but think I got the one you are after using the following measure.

=CALCULATE(SUM(ItemDemand[Qty]),
           FILTER(Relationship,
                  Relationship[Child Item] IN VALUES(Relationship[Child Item])))

Without adding this filter argument, the filtering only flows downwards in your relationship diagram.

Upvotes: 0

Related Questions