cancel
Showing results for
Did you mean:
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:

 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 8
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:

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,
Ben

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:

 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
Regular Visitor

## Re: Row over row logic

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

Established Member

## Re: Row over row logic

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

Let me know if this works.

Ben
Highlighted
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!
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?

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.