Average value not using row count

[Robert-bpd]

Hi,

I have a table structure like this:

Table: Order

OrderId	Quantity
1	10
2	2

Table: OrderItem

OrderId	ItemId	Value
1	1	15
1	2	6
2	1	12

Table: Item

ItemId	Name
1	Door
2	Window

Now I want to have a visual to show the average sold value of the items. But it should take the quantity into account:

Item	Average value
Door	(15*10 + 12*2) / 12 = 14.5
Window	(6*10) / 10 = 6

How can  I create the "Average value" column?

Thx!

[az38 · Accepted solution]

@Robert-bpd 

as I could imagine you need smth like

Column = DIVIDE(calculate(SUMX('Table (2)', 'Table (2)'[Value]*RELATED('Table'[Quantity]))), calculate(SUMX('Table (2)', 'Table (2)'[Value])))

but you need check columns depending on your business logic

[Robert-bpd]

Thx! @az38 and @Anonymous 

I believe this would be the correct version:

Test = DIVIDE(CALCULATE(SUMX(OrderItem; OrderItem[Value] * RELATED(Order[Quantity]))); CALCULATE(SUMX(OrderItem; RELATED( Order[Quantity]))))

[Anonymous]

@az38, do you need calculate around the SUMX expressions? If so, why?

@Robert-bpd, az38:s solutions looks good, but of course it requires proper releations between the tables.

[az38]

@Robert-bpd 

whydo you divide Door by 12?