Iteration over iteration in calculated column

[AntonioCarl]

Hi there, 

I've got table like below. 

I need to create calculated column with something I've got problem with.

For each ORDER_ID I want to sum over iteration over DELIVERY_NOTE_ID where I calculate average of  ORDER_QUANTITY. 
Basically, in example, I want to go through ORDER_ID = 131371 and iterate over DELIVERY_NOTE_ID = 974441 and calculate average of ORDER_QUANTITY. For DELIVERY_NOTE_ID = 974441 it should return 2 and through DELIVERY_NOTE_ID = 974440 I should get 4. Now I come back to  ORDER_ID = 131371 and sum those values to get 6. 

It's easy to do it in measure but I'm stuck in calculated column. 

SUMX(

    VALUES( Table[DELIVERY_NOTE_ID] ),

    CALCULATE( AVERAGE(Table[ORDER_QUANTITY] ) ) 

)

ORDER_ID	DELIVERY_NOTE_ID	ORDER_QUANTITY	DELIVERY_NOTE_QUANTITY
131371	974441	2	1
131371	974441	2	1
131371	974440	4	4

[Daniel29195 · Accepted solution]

@AntonioCarl 

output : 

calcualtion : 

Column = 
var oi =  tbl3[ORDER_ID]
var dni  = tbl3[DELIVERY_NOTE_ID]
var ds = 
FILTER(
    tbl3,
    tbl3[ORDER_ID]= oi && tbl3[DELIVERY_NOTE_ID]= dni
)

var res = 
SUMX(
    ds,
    tbl3[ORDER_QUANTITY]
)/
COUNTROWS(ds)
return res

let me know if this works for you . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠