Total in table based on other column

[TimvMechelen]

Hi all,

First of all, I have a material master table which displays if it's a "Old" or "New" material. An "Old" material means that there is a "New" article, which is a replacement for the old article.

I'd like to have a table where I can see the stock of the new material and the stock of the old material in the same row, see below.

Currently I have 2 tables, but I don't know how to write the measure for the "Stock Old material number" column:

I'd like to have the following result:

Material Number	Old/New material	Old material number	Total stock (New)	Total stock (old)
4	New	1	25	100
5	New	2	5	80
6	New		80	0
7	New		3	0
8	New	3	120	5
10	New		20	0
11	New	9	0	10

Link to Power BI example file: https://www.dropbox.com/s/x6tlsu9vmzxr992/Supply%20Chain%20Control%20-%20Sample%20data%20Test%201.pb...

Help with this would be greatly appreciated! 

[amitchandak]

@TimvMechelen , Try like this Join Table 2 and Table 1 two times. Active join material no and material no

and inactive join material No and old material  no

then create measure

new Stock = calculate(Sum(Table[Stock]) , not(isblank(Table[Material no])) )

old Stock = calculate(Sum(Table[Stock]), userelationship(Table[Old Material No], Table2[Material no])  , not(isblank(Table[Old Material No])) )

[TimvMechelen]

@amitchandak Is there a way to do this without joining the tables 2 times?

[TimvMechelen]

@amitchandak I understand your answer now, I created the relationship between the 2 tables:
However, I still do not get the right answer: