Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 NumberQuantity 
A4
B4
C5
C4
B5

 

Parts Scrapped

PartsQuantity
A1
B1
B2
A1
A1
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

MaterialQuantityScrapping date
Q3390.60011/4/2022
Q966-88.54011/4/2022
Q231-11511/4/2022
Q545-22.48011/4/2022
S31880911/4/2022
Q407-4,14611/4/2022
Q239-117.89611/4/2022
Q484-1,457.08011/4/2022
Q958-501.53011/4/2022
A354-45011/4/2022
L160111/4/2022
L350111/4/2022
L314111/4/2022
L314111/4/2022
L900111/4/2022
L640111/4/2022
L630111/4/2022
L480111/4/2022
L190311/4/2022
L200211/4/2022
L350111/4/2022
L470111/4/2022
B0425011/4/2022
L240111/4/2022
L316211/4/2022
L320211/4/2022
L5001211/4/2022
L4901211/4/2022
L4301211/4/2022
L5001211/4/2022
L4901211/5/2022
L4301211/5/2022
L1601611/5/2022
L350111/5/2022
L314111/5/2022
L314111/5/2022
L900111/5/2022
L640111/5/2022
L630111/5/2022
L480111/5/2022
L190311/5/2022
L200211/5/2022

 

 

Labels Made

MaterialQuantityScrapping date
Q3390.6001/4/2022
Q966796.8601/4/2022
Q2313451/4/2022
Q545899.2001/4/2022
S3188091/4/2022
Q40712,4381/4/2022
Q239589.4801/4/2022
Q4842,914.1601/4/2022
Q9584,012.2401/4/2022
A35429,7001/4/2022
L160521/4/2022
L35021/4/2022
L314271/4/2022
L314331/4/2022
L90051/4/2022
L64041/4/2022
L630631/4/2022
L480891/4/2022
L190811/4/2022
L200541/4/2022
L350271/4/2022
L470271/4/2022
B0421,3501/4/2022
L240271/4/2022
L316541/4/2022
L320541/4/2022
L5003241/4/2022
L4903241/4/2022
L4303241/4/2022
L5003241/4/2022
L4903241/5/2022
L4303241/5/2022
L1604321/5/2022
L350271/5/2022
L314271/5/2022
L314271/5/2022
L900271/5/2022
L640271/5/2022
L630271/5/2022
L480271/5/2022
L190811/5/2022
L200541/5/2022

 

 

amitchandak
Super User
Super User

@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
Super User
Super User

what do you mean by discarded?

Anonymous
Not applicable

@FreemanZ@tamerj1 

Sorry about that, discarded material = scrap material.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors