Average value based on two columns in another table

[bklyn3]

Hello PowerBI community

I have two tables PreviousOrders and FutureOrders.

PreviousOrder

Facility	Processing Time	OrderType
A	13	Express
A	15	Normal
B	19	Express
C	10	Normal
A	11	Express
B	15	Express
B	13	Express
C	14	Normal

FutureOrders.

Facility	OrderType	ExpectedTime
A	Express	=averageif(A and Express in First table)
B	Express
C	Express
C	Normal
A	Express
A	Normal
B	Express
C	Normal

I would like the ExpetedTime in the “FutureOrders” table to be the "Processing Time" for that facility and order type based on the data in PreviousOrder.

Is there an easy way to do this in DAX? 

Thank you

[jeroendekker]

Ì know this is solved but I would like propose a alternative without DAX in Power Query

Merge both tables

Expand using aggregate option --> sum (average not directly available)

Change List.Sum to List.Average in the formula.

Done, no many-2-many needed.

[bklyn3]

Thank you

[tamerj1 · Accepted solution]

Hi @bklyn3 

you build a many to many relationship between the two tables based on the facity column. Then New column

AVERAGEX (

FLITER (

RELATEDTABLE ( PreviousOrder ),

PreviousOrder[OrderType] = FutureOrders[OrderType] 

),

PreviousOrder[Processing]
)