Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I've 2 tables.
ITEM | Qty |
1A | 8 |
2A | 4 |
3A | |
4A | 5 |
5A | |
6A | 6 |
7A | 6 |
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!
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
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)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
81 | |
65 | |
65 | |
61 |
User | Count |
---|---|
170 | |
114 | |
102 | |
73 | |
69 |