Forum Discussion

AntonioCarl's avatar
AntonioCarl
Helper I
2 years ago
Solved

Iteration over iteration in calculated column

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_IDDELIVERY_NOTE_IDORDER_QUANTITYDELIVERY_NOTE_QUANTITY
13137197444121
13137197444121
13137197444044
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🤠

     

1 Reply

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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! 🤠