Forum Discussion
Row over row logic
A bit stumped here.
Trying to enhance a dataset with a column that uses some logic.
The data is coming from general ledger entries with the the two (or more) accounts an document posts to. What I need to do is determine which lines of a document contain the entirety of an action. I know when the total of the rows = 0, that's a complete action.
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 | 10 |
| 4 | 5 | -40 |
The data is in order, so starting from row 1 we see that adding row 2 sums to 0 thus one entire action. Similarly, 3 & 4, one entire action. 5, 6, and 7 one action. And so one.
Expected results (label/count actions or flag row):
| Doc | Line | Amount | Action | Flag start |
| 1 | 1 | 10 | 1 | X |
| 1 | 2 | -10 | 1 | |
| 2 | 1 | 20 | 2 | X |
| 2 | 2 | -20 | 2 | |
| 3 | 1 | 10 | 3 | X |
| 3 | 2 | 10 | 3 | |
| 3 | 3 | -20 | 3 | |
| 4 | 1 | 30 | 4 | X |
| 4 | 2 | -30 | 4 | |
| 4 | 3 | 30 | 5 | X |
| 4 | 4 | 10 | 5 | |
| 4 | 5 | -40 | 5 |
Any ideas?
Thanks!
8 Replies
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:
Flag1 =IF(OR(LOOKUPVALUE(Sheet1[Amount],Sheet1[Index],Sheet1[Index]-1) < 0,Sheet1[Index] = 1),1)That gives me the following output:Let me know if this works for you. I also got this output with a calculated measure instead. Let me know if you'd like to see this option as well.Thanks,BenClose!
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
