Lookup and Sum of Values from Another Table

[newpbiuser01]

Hello,

I'm trying to lookup values from Table 1 - match to similar values in Table 2 and if a match is found, get a sum of the total amount, and if no match found, return a 0 or a blank. Since we have multiple instances of the names in each column, I created a many-many relationship with the Table 1 set to filter Table 2. However, when I try to add the sum of the Amount column to the Table 1, it looks like Table 1 gets filtered too:

Table 1:

Name

Andy

Bob

Joe

Mike

Susan

Table 2:

Name	Amount
Andy	10
Andy	100
Bob	1000
Joe	500
Peter	10
Peter	5
Sally	50

Expected Result in Table 1:

Name	Amount
Andy	110
Bob	1000
Joe	500
Mike	0
Susan	0

However, what I do get for Table 1 is:

Name	Amount
Andy	110
Bob	1000
Joe	500

How do I do the lookup and sum without my primary table getting filtered out? I'd really appreciate any help. 

[v-easonf-msft · Accepted solution]

Hi， @newpbiuser01 

Many-to-many relationship is not recommended here.

Please modify it as follows.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@newpbiuser01 , Make it bi directional and try. Having a many to many it not a good idea

Create a common table and try

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...