Finding Matching value within the same column

[rishtin]

Hello,

I have a dataset with a column Amount that has positive and negative $ amounts for transactions. I want to take each negative amount and find its equavalent positive amount and mark both of these transactions. I can't wrap my head around the logic. Some help would be appreciated.

Here's the type of dataset that i have. I would like to mark both TrxID 101 and 102 as "Matched" or something in a separate calculated column.

TrxID	Dept	Vendor	Amount
101	D123	V009	92.25
102	D123	V009	-92.25
103	D124	V009	23.22

thanks

[v-jayw-msft]

Hi @rishtin ,

What if the amount of ID 103 is 92.25 or -92.25? All three rows get matched?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rishtin]

@v-jayw-msftit is possible to have a matching number in a different department but we will be looking for matching values for the same vendor in the same department.

[amitchandak]

@rishtin 

Make sure you have id column or

create a index column

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Get the same index positive value

Same index = max(filter(table, abs(table[value]) =earlier(table[value]) && table[value]<0),table[index])

You can add conditions like

&& table[account_no] =earlier(table[account_no]) 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490