Calculating value from different rows in the same data

[Anonymous]

Dear all, 

I am facing a data summary problem with my data which has duplicated values

The system automatically receive from two different sources the data a a different timing 

and as of today we do not have possibility to preprocess the data before it get into the data base 

Following is the data I have to get from the DB and do my reports in Power bi

Data from the first sytem 

Companie	`Sub companie	Product	Quantity
A	A	P1	100
A	A	P2	200
A	A-1	P1	20
A	A-2	P1	50
A	A-1	P2	40
A	A-2	P2	100
B	B	P1	100
B	B	P2	200
B	B-1	P1	20
B	B-2	P1	50
B	B-1	P2	40
B	B-2	P2	100

Data from the second system 

Companie	`Sub companie	Product	Quantity		True Quantiy
A	A-1	P1	20		20
A	A-2	P1	50		50
A	A-1	P2	40		40
A	A-2	P2	100		100
B	B-1	P1	20		20
B	B-2	P1	50		50
B	B-1	P2	40		40
B	B-2	P2	100		100

In order to report the right numbers for each sub-company  

I need to create a Calculated column or measure that 

allows me to substract  the quantity that is included in its company.

For example for the product P1

Quantiy of Sub-company A =100-(20+50) = 30

So the final result would look like this

Companie	`Sub companie	Product	Quantity		True Quantiy
A	A	P1	100		30
A	A	P2	200		60
A	A-1	P1	20		20
A	A-2	P1	50		50
A	A-1	P2	40		40
A	A-2	P2	100		100
B	B	P1	100		30
B	B	P2	200		60
B	B-1	P1	20		20
B	B-2	P1	50		50
B	B-1	P2	40		40
B	B-2	P2	100		100
A	A-1	P1	20		20
A	A-2	P1	50		50
A	A-1	P2	40		40
A	A-2	P2	100		100
B	B-1	P1	20		20
B	B-2	P1	50		50
B	B-1	P2	40		40
B	B-2	P2	100		100

I would appreciate if someone here can help me to solve this issue. 

Thank you

Koffi

[Greg_Deckler]

I would break the first table into 2 tables. The first query would just get all rows where the "Companie" equaled teh "Sub companie". The other query would get the reverse of that. Then you could relate the tables and the problem should become elementary using a CALCULATE coupled with a RELATEDTABLE filter.