Dax to sum matched transactions

[markpatton]

I'm looking for a measure that will return the sum of matched transactions in a table. 

For the following table. I want to sum the values for records with account=2000 that have a corresponding (opposite) value with account = 11000

ID	Account	Value	Transaction ID
1	11000	2000	a
2	11000	3000	b
3	2000	-2000	a
4	2000	4000	c
5	11000	5000	d
6	2000	-5000	d
7	3000	2000	e
8	4000	3000	f

With the measure coded to account 11000 and account 2000 I would want the value to be returned to be -7000. Basically sum of value for row 3 and 6 because they have corresponding values in rows 1 and 5 for account 11000.

[Nathaniel_C · Accepted solution]

Hi @markpatton , try this

Matching values =
VAR _11Acct = 11000
VAR _table11 =
    FILTER ( 'Table', 'Table'[Account] = _11Acct )
VAR _2Acct = 2000
VAR _addcolumn =
    CALCULATETABLE (
        ADDCOLUMNS (
            _table11,
            "LOOK", LOOKUPVALUE ( 'Table'[Value], 'Table'[Value], [Value] * -1 )
        ),
        FILTER ( 'Table', 'Table'[Account] = _2Acct )
    )
VAR _sum =
    SUMX ( _addcolumn, [LOOK] )
RETURN
    _sum

That was fun!
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[markpatton]

Worked Great  - Thanks!

Mark

[Nathaniel_C]