cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aron_Moore Established Member
Established Member

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:

DocLineAmount
1110
12-10
2120
22-20
3110
3210
33-20
4130
42-30
4330
4410
45-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):

DocLineAmountActionFlag start
11101X
12-101 
21202X
22-202 
31103X
32103 
33-203 
41304X
42-304 
43305X
44105 
45-405 

 

Any ideas?

 

Thanks!

8 REPLIES 8
bhpage Regular Visitor
Regular Visitor

Re: Row 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:

 

Row over Row 1.png

 

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:
Row over Row 2.PNG
 
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,
Ben
 
Aron_Moore Established Member
Established Member

Re: Row over row logic

Close!

 

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:

DocLineAmount
1110
12-10
21-20
2220
3110
3210
33-20
4130
42-30
4330
44-40
4510
bhpage Regular Visitor
Regular Visitor

Re: Row over row logic

Can you include where you expect the flags in this updated scenario?

Aron_Moore Established Member
Established Member

Re: Row over row logic

Whichever result is easiest to produce.

 

I could use any of these result columns:

DocLineAmountAction#Flag(start)Flag(end)
11101X 
12-101 x
21-202x 
22202 x
31103x 
32103  
33-203 x
41304x 
42-304 x
43305x 
44-405  
45105 x
bhpage Regular Visitor
Regular Visitor

Re: Row over row logic

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:
 
Row over Row 3.PNG
 
Let me know if this works.
 
Ben
Highlighted
Aron_Moore Established Member
Established Member

Re: Row over row logic

Awesome!

 

I modified to a calculated column and switched to flagging the start.

 

Flag = IF(SUMX(FILTER(ALL(Table1),Table1[Index]< EARLIER(Table1[Index])),[Amount]) = 0,1)
 
Thanks again!
Aron_Moore Established Member
Established Member

Re: Row over row logic

Uh oh.

 

Added the column to the actual data, ~550k rows, and Power BI just spins "Working on it"....

 

Anyone have tips for optimization? Maybe replicate this logic with M? But how?

bhpage Regular Visitor
Regular Visitor

Re: Row over row logic

Not sure what your data architecture/data structure looks like, but if I were you, I would try to put a calculation like this on the DB side. It's at the lowest grain of data and could just be incorporated into your driving table.