topic Average value based on two columns in another table in DAX Commands and Tips

Average value based on two columns in another table

bklyn3 — Fri, 20 May 2022 17:50:03 GMT

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

Re: Average value based on two columns in another table

tamerj1 — Fri, 20 May 2022 18:07:44 GMT

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]
)

Re: Average value based on two columns in another table

bklyn3 — Fri, 20 May 2022 18:25:57 GMT

Thank you

Re: Average value based on two columns in another table

jeroendekker — Fri, 20 May 2022 19:37:22 GMT

Ì 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.