Calculate Defect Rate

[Anonymous]

Hello,

I've 2 tables. 

• TABLE 1: Defect Data Table

ITEM	Qty
1A	8
2A	4
3A
4A	5
5A
6A	6
7A	6

• TABLE 2: Item Receive Data Table

Item	Qty
1A	15
2A
3A
4A	14
5A	51
6A
7A	9

Expected Result: Consider only items, which has QTY data from both table. (like consider 1A, 4A and 7A only here).

Defect rate = SUM(Defect table (QTY))/ Sum(Item Receive table (QTY))

I need to project defect rate on the line and clustered coulmn chart. Please advise!

[v-lili6-msft]

hi, @Anonymous 

Create the relationship between Defect Data Table with Item Receive Data Table by Item field.

Then use DIVIDE Function to add a measure:

Defects = DIVIDE(SUM(Table16[Qty]),SUM(Table17[Qty]))

If not your case, please share your expected output.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

I already had an answer in table (by having filter: Qty (defect table) and Qty (Receive Table) is NOT BLANK) but to have it this defect rate by Vendor on line and bar chart, it's calculating total sum of QTY on both the table and showing a wrong result. 

As shown in below image, I got the result in table (23.43 % is expected outcome) but not on the chart (05.16 % not the correct one). 

Hope this help. Can you please advise?

[v-lili6-msft]

hi, @Anonymous 

Could you share your sample pbix file for us to have a test, It is difficult to find out the reason from the screenshot.

You can upload it to Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin

[Greg_Deckler]

You should be able to do something like:

Measure = 
VAR __table2 = FILTER('Table17',NOT(ISBLANK([Qty])))
VAR __table3 = ADDCOLUMNS(__table2,"__defects",SUMX(RELATEDTABLE('Table16'),[Defects]))
VAR __table4 = FILTER(__table3,NOT(ISBLANK([__defects])))
RETURN
DIVIDE(SUMX(__table4,[__defects]),SUMX(__table4,[Qty]),0)