Scrap Ratio (Dax formula that uses data from two different tables)

[Anonymous]

Hello,

I could really use some help. I want to write a DAX formula to determine each part's scrap ratio. However, I cannot figure out why it is not calculating. I have a list of labels made in one table and a list of parts scrapped in another. I only want to count the part numbers that are being discarded. Can anyone help?

Labels Made

Part Number	Quantity
A	4
B	4
C	5
C	4
B	5

Parts Scrapped

Parts	Quantity
A	1
B	1
B	2
A	1
A	1

[Anonymous]

I need the information to stay on the table. Power Bi will sum the scrap, but it is not summering my labels made. I can only assume it is because of the mass quantity of data, but that does not make sense. I tried clearing all filters, but that did not fix the problem. I included a little more detail about my problem.

Scrap Made

Material	Quantity	Scrapping date
Q339	0.600	11/4/2022
Q966	-88.540	11/4/2022
Q231	-115	11/4/2022
Q545	-22.480	11/4/2022
S318	809	11/4/2022
Q407	-4,146	11/4/2022
Q239	-117.896	11/4/2022
Q484	-1,457.080	11/4/2022
Q958	-501.530	11/4/2022
A354	-450	11/4/2022
L160	1	11/4/2022
L350	1	11/4/2022
L314	1	11/4/2022
L314	1	11/4/2022
L900	1	11/4/2022
L640	1	11/4/2022
L630	1	11/4/2022
L480	1	11/4/2022
L190	3	11/4/2022
L200	2	11/4/2022
L350	1	11/4/2022
L470	1	11/4/2022
B042	50	11/4/2022
L240	1	11/4/2022
L316	2	11/4/2022
L320	2	11/4/2022
L500	12	11/4/2022
L490	12	11/4/2022
L430	12	11/4/2022
L500	12	11/4/2022
L490	12	11/5/2022
L430	12	11/5/2022
L160	16	11/5/2022
L350	1	11/5/2022
L314	1	11/5/2022
L314	1	11/5/2022
L900	1	11/5/2022
L640	1	11/5/2022
L630	1	11/5/2022
L480	1	11/5/2022
L190	3	11/5/2022
L200	2	11/5/2022

Labels Made

Material	Quantity	Scrapping date
Q339	0.600	1/4/2022
Q966	796.860	1/4/2022
Q231	345	1/4/2022
Q545	899.200	1/4/2022
S318	809	1/4/2022
Q407	12,438	1/4/2022
Q239	589.480	1/4/2022
Q484	2,914.160	1/4/2022
Q958	4,012.240	1/4/2022
A354	29,700	1/4/2022
L160	52	1/4/2022
L350	2	1/4/2022
L314	27	1/4/2022
L314	33	1/4/2022
L900	5	1/4/2022
L640	4	1/4/2022
L630	63	1/4/2022
L480	89	1/4/2022
L190	81	1/4/2022
L200	54	1/4/2022
L350	27	1/4/2022
L470	27	1/4/2022
B042	1,350	1/4/2022
L240	27	1/4/2022
L316	54	1/4/2022
L320	54	1/4/2022
L500	324	1/4/2022
L490	324	1/4/2022
L430	324	1/4/2022
L500	324	1/4/2022
L490	324	1/5/2022
L430	324	1/5/2022
L160	432	1/5/2022
L350	27	1/5/2022
L314	27	1/5/2022
L314	27	1/5/2022
L900	27	1/5/2022
L640	27	1/5/2022
L630	27	1/5/2022
L480	27	1/5/2022
L190	81	1/5/2022
L200	54	1/5/2022

[amitchandak · Accepted solution]

@Anonymous , Create a common Parts Table and then create a measure

divide(Sum(Scrapped[Quantity]), Sum(Labels [Quantity]))

Analyze with common table 

Parts = distinct(union(distinct(Table1[Parts]),distinct(Table2[Parts])))

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

[FreemanZ]

what do you mean by discarded?

[Anonymous]

@FreemanZ, @tamerj1 

Sorry about that, discarded material = scrap material.