Forum Discussion

Michsara's avatar
Michsara
Regular Visitor
4 years ago

Relating two tables with missing matches

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!

 

 

 

 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      Michsara
      Regular Visitor
      4 years ago

      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