Forum Discussion

bklyn3's avatar
bklyn3
Advocate II
4 years ago
Solved

Average value based on two columns in another table

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

 

 

DAX Calculation
DAX Command
DAX Data Table
Help with DAX Measure
help.me
  • tamerj1's avatar
    tamerj1
    4 years ago

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

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

  • jeroendekker's avatar
    jeroendekker
    Frequent Visitor
    4 years ago

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