Identify Duplicates +/- but only if -

[Barry_Sophus]

I need to identify all negative amounts.

If the column contains a corresponding positive amount of the same value I need to identify them as well. 

Please help. Thank you in advance.

Amount
1	X
1	X
1	X
-1	X
-1	X
2
2
2
2
-3	X
4	X
4	X
-4	X

[DataNinja777 · Accepted solution]

Hi @Barry_Sophus ,

You can generate the desired output by creating a calculated column using the following formula:

NegativePositiveCheck = 
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Amount]
                = - EARLIER ( 'Table'[Amount] ) -- Find the opposite value using EARLIER
        )
    ) > 0,
    "X",
    BLANK ()
)

Out of curiosity, what is the purpose of this requirement? Are you aiming to clear balance sheet line items with matching balances? If so, the formula provided is not a complete solution. For proper matching clearance, you need to consider not only the values and signs but also ensure the debits and credits match in even numbers, starting with the earliest dates. In accounting, clearing matching balances requires a more refined formula than simply matching based on value and sign alone.

I have attached an example pbix file:

[lbendlin]

What would be the expected outcome be based on your sample data?

[Barry_Sophus]

The X's are the expected outcome.