Forum Discussion
Solution SpecialistRow over row logic
Hi,
I think there are two ways to accomplish this, assuming your data structure looks like what you've provided. To me, this feels like this flag should be at the grain of the data, so it could be included as a calculated column in your data source. So, what I've done is sorted the data by Doc, and then Line, and then just created an Index from within the Power Query Editor, with the "starting at 1" option:
From there, I created a calculated column with the following logic:
Solution SpecialistClose!
Unfortunately, I messed up my sample data. The real data doesn't always have the negative values last, hence the need to sum until we get a zero total.
Updated sample data:
| Doc | Line | Amount |
| 1 | 1 | 10 |
| 1 | 2 | -10 |
| 2 | 1 | -20 |
| 2 | 2 | 20 |
| 3 | 1 | 10 |
| 3 | 2 | 10 |
| 3 | 3 | -20 |
| 4 | 1 | 30 |
| 4 | 2 | -30 |
| 4 | 3 | 30 |
| 4 | 4 | -40 |
| 4 | 5 | 10 |
Can you include where you expect the flags in this updated scenario?
- Aron_Moore
Whichever result is easiest to produce.
I could use any of these result columns:
Doc Line Amount Action# Flag(start) Flag(end) 1 1 10 1 X 1 2 -10 1 x 2 1 -20 2 x 2 2 20 2 x 3 1 10 3 x 3 2 10 3 3 3 -20 3 x 4 1 30 4 x 4 2 -30 4 x 4 3 30 5 x 4 4 -40 5 4 5 10 5 x Got it. I think this works, but I created this as a measure now instead of a calculated column like the previous approach. I set this up to represent your "Flag(end)." This could be easily modified for the other scenario if that's desired, though. Here's the new calc:
Flag Final = IF(SUMX(FILTER(ALL(Sheet1),Sheet1[Index]<= SELECTEDVALUE(Sheet1[Index])),Sheet1[Amount]) = 0,1)And the output:Let me know if this works.Ben