How to sum related to Many to Many relationship

[bourne2000]

Hi

I have two tables,

Table 1:

ProductID	Paid Amount
A	10
B	20
C	30
D	40
E	50
F	60
G	70
H	80

Table 2:

ProductID	TotalDue
A	30
B	40
C	50
D	60
K	70
L	80
M	30
N	40
I	50
O	60

Both Table 1 & Table 2 has connected with Product ID and it is Many to Many Relationships

What I need?

1) Wanted to calculate Remaining Amount for the productID presented in Table 1

I used below dax

Remaining Amount = sum(Table2[Total Due]) - sum(Table1[Paid Amount]) 

However it is not working

Expected Output

ProductID	Paid Amount	Total Due	Remaning Amount
A	10	30	20
B	20	40	20
C	30	50	20
D	40	60	20

2) Wanted to print the Table 1 records which is not presented in the Table 2

Expected Output

ProductID	Paid Amount
E	50
F	60
G	70
H	80

I don't want to use any bridge table, possible to solve through DAX?

Kindly advise

[SamInogic · Accepted solution]

Hi,

As per our understandings you are looking for DAX formulae to get required data and have Many many to relationship in your tables, so have created sample data as per your requirement as follows:

Table 1

 
Table 2

Relationship Between table will be as Following 

You can create a new column [Total Due] by using below DAX Expression 

Total Due = LOOKUPVALUE('Table 2'[TotalDue],'Table 2'[ProductID],'Table

 1'[ProductID])

Then create a new column for Remaining Amount using below DAX expression,

Remaining Amount = IF('Table 1'[Total Due]-'Table 1'[Paid Amount]>=0,'Table

 1'[Total Due]-'Table 1'[Paid Amount],0)

Regarding second question to find non-matching records from table 2 you can create new column in Table 1 with below DAX expressions:

ExistInTable2 = IF(ISBLANK(LOOKUPVALUE('Table 2'[ProductID],'Table 2'[ProductID], 'Table 1'[ProductID])), "No","Yes")

And to display only records which are not present in Table 2 you can apply filter on the table visual as ExistInTable as “No”.

Please refer to the below screenshot for the same,

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/