Forum Discussion
Calculated Column looking up values in same table
LasseL Thanks for confirming that !!
Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)
ActiveOperation = VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1) RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)
Hi there Pattem, thanks for your quick response.
Certainly, here you go, a sample in table form, last column is the expected output.
| Prod_ Order No_ | Line No_ | Qty_ to Consume | ActiveOperation |
| LP026221 | 40000 | 0 | 0 |
| LP026221 | 50000 | 0 | 0 |
| LP026221 | 70000 | 0 | 0 |
| LP026221 | 80000 | 0 | 0 |
| LP026221 | 100000 | 0 | 0 |
| LP026221 | 110000 | 4900 | 1 |
| LP026221 | 130000 | 140 | 0 |
| LP026221 | 150000 | 120 | 0 |
| LP026221 | 160000 | 700 | 0 |
| LP026221 | 180000 | 525 | 0 |
| LP026222 | 40000 | 0 | 0 |
| LP026222 | 50000 | 0 | 0 |
| LP026222 | 70000 | 0 | 0 |
| LP026222 | 80000 | 144 | 1 |
| LP026222 | 100000 | 240 | 0 |
| LP026222 | 110000 | 2016 | 0 |
| LP026222 | 130000 | 576 | 0 |
| LP026222 | 150000 | 120 | 0 |
| LP026222 | 160000 | 288 | 0 |
| LP026222 | 180000 | 216 | 0 |
Regarding type of attributes; Prod_ Order No_ is a text string, the rest are numeric values.
LasseL Just want to confirm - You want to flag "ActiveOperation" as 1 for first non-zero value within each Prod_Order_No group and others as 0. Is that correct ?
That is very well formulated and correct, as a calculated column.
LasseL Thanks for confirming that !!
Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)
ActiveOperation = VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1) RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)