Relating two tables with missing matches

[Michsara]

I'm tearing my hair out trying to work this out!  I have 2 tables - one has targets and the other has actuals.  I've tried concatenating a column and have an inactive relationship.  I then used two calculations to get the related targets and actuals:

TARGET = CALCULATE(SUM(Targets Table[TARGET]),USERELATIONSHIP('TARGETS TABLE'[MERGE],ACTUALS TABLE[MERGE]))

ACTUALS = CALCULATE(SUM(Actuals Table[Revenue]), USERELATIONSHIP('TARGETS TABLE'[MERGE],ACTUALS TABLE[MERGE]))

The join is many to many, both.

My problem is that if there is no TARGET, but there is an amount under ACTUAL, or vice versa, it won't show all the data.

Hopefully someone can help before I go bald!

[Greg_Deckler]

@Michsara Not sure I understand exactly but try adding + 0 to the end of both formulas. It's a simple trick so that you don't return null/blank and therefore your rows show up.

[Michsara]

Hi @Greg_Deckler 

Unfortunately this hasn't helped.  I've mocked up the example in PowerBi. Below are my 2 tables and the joins.

Below is my workbook - for some reason there are some targets missing and I can't work out why.  If there is a target but no actual to match, it doesn't show either, but if there is an actual but no target it works