Forum Discussion
Calculate Defect Rate
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!
4 Replies
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)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
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?
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
