2

We have an issue with power BI where we cannot use a many-many relationship to do calculations.

End goal is Column E: enter image description here

Initial Table #1:

Inventory

Initial Table #2:

Purchases

When I use the Average Price per Area of Initial Table #2 on the Initial Table #1[Item] as the Rows element, I will get a warning: Relationships between tables maybe needed. But the issue is Power Bi in Excel does not support a many-many relationship yet.

As can be seen, the Pivot Table is not displaying correctly as there is no item green. I have another database with more SKUs and it's all displaying #NUM there because it cannot reference the items despite having the same name.

enter image description here

Pherdindy
  • 1,168
  • 7
  • 23
  • 52

1 Answers1

4

Create a third table that contains unique items, i.e, 

Table 3:

Column "Item"
Blue
Green
Red
...

Then connect both initial tables to it using field "Item".

Put "Item" from the third table on pivot rows, and your report should work (assuming your DAX formulas are correctly written).

RADO
  • 7,733
  • 3
  • 19
  • 33