Identify data not used/matched

[Mr_Stern]

I work with a number of large reconciliations where I have to match then entries from one table (actual) to the entires in several other tables (source).  

There are a few items I need to accomplish in this process.

1)  I need to identify in Source, the corrisponding row in Actual

2)  I need to identify in Actual, What row has been used in Source

3)  I need to identify in Actual, What rows are not included in Source

4)  As a complication, the value in Actual will always be 100% of the value, however the Source, could be made up of two rows. (see Transaction 303 in the below example)

As an example below I will just use one source, though it really comes from four.

Source Data                                                                   Actual Data

Row        Transaction       Value                                     Transaction                 Value

1               101                   200                                        101                             200

2               102                   150                                        420                             275

3               103                   125                                        104                              200

4               104                   200                                         300                              180

5                300                  180                                         303                              750

6                301                   250                                        103                              125

7                303                   500

8                303                   250

9                420                   275

My problems are below:

1)  I need to identify in Source, the corrisponding row in Actual

If I do a 'lookupvalue' in Actual in transactions, I can ID the row, however when I run into transaction 303, I need to identify the result as both row 7 &  row 8.  any thoughts?

2)  I need to identify in Actual, What row has been used in Source

I can just to a 'lookupvalue' in Source to actual using the row that has just been identified. in step 1.

3)  I need to identify in Actual, What rows are not included in Source

Looking at it, step two would then provide me the results needed through no results.

So I guess I am looking to resolve my issue in step 1, and then if any other ideas or insights would be great.

My data sets are a few  thousand lines long, and there are more complexities than this, however it is a good starting point.

Thanks!

[Anonymous]

@Mr_Stern Not sure if I understood it correctly but to start with create this calculated column in your source table 

IsInActual = 
VAR _totalByTransaction = CALCULATE(SUM(Source[Value]),ALLEXCEPT(Source,Source[Transaction]))
VAR _check = LOOKUPVALUE(Source[Transaction],Actual[Transaction],Source[Transaction],Actual[Value],_totalByTransaction)
RETURN IF(_check <> BLANK(),"Exist","Does not exist")

I'm doing the lookup of transaction and total value per transaction in actual table. This way transaction 303 does exist in actual.