Forum Discussion

Mr_Stern's avatar
Mr_Stern
Frequent Visitor
6 years ago

Identify data not used/matched

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 accompl...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.