Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am VERY new to PowerBI and have been struggling with calculating a defect rate for weeks. Please help!
I have a table that has R (regular) and W (warranty) orders. I want to divide the InvoiceQty of the W orders by the InvoiceQty of the R orders to get the defect rate. I have tried many approaches and variations. Here is my current formula in the Query Editor:
DefectRate = DIVIDE(CALCULATE(SUMX([InvoiceQty], [OrderType] = "W"),CALCULATE(SUMX([InvoiceQty],[OrderType]="R"))
The current error it gives me is a Token RightParen Expected Error. I have no idea what this means.
Solved! Go to Solution.
@Anonymous
Hi, are using DAX in Query Editor (This use Power Query Language).
To obtain this you can create a New Measure.
DEFECTRATE = DIVIDE ( CALCULATE ( SUM ( [InvoiceQty] ), FILTER ( YOURTABLE, YOURTABLE[OrderType] = "W" ) ), CALCULATE ( SUM ( [InvoiceQty] ), FILTER ( YOURTABLE, YOURTABLE[OrderType] = "R" ) ) )
Regards
Victor
Lima - Peru
@Anonymous
Hi, are using DAX in Query Editor (This use Power Query Language).
To obtain this you can create a New Measure.
DEFECTRATE = DIVIDE ( CALCULATE ( SUM ( [InvoiceQty] ), FILTER ( YOURTABLE, YOURTABLE[OrderType] = "W" ) ), CALCULATE ( SUM ( [InvoiceQty] ), FILTER ( YOURTABLE, YOURTABLE[OrderType] = "R" ) ) )
Regards
Victor
Lima - Peru
So I am taking this formula a few steps further and I am stuck. Below I have added in DefectReason that is not AB Damage, I also need to add in that the DefectReason is not Concealed Damage or Goodwill. I tried with a comma but it does not like it. How can I add more?
UnfilterDefectRate = DIVIDE(
CALCULATE(
SUM ( vSalesMaster[WarrDetail.InvoiceQty] ),
FILTER (vSalesMaster,vSalesMaster[OrderType] = "W" &&
vSalesMaster[WarrDetail.DefectReason] <> "AB Damage" )
),
CALCULATE(
SUM ( [InvoiceQty] ),
FILTER (VSalesMaster,vSalesMaster[OrderType] = "R")
)
)
Thank you so much for the quick response. I have made the adjustments and am getting a Token RightParen expected error.
= Table.AddColumn(vSalesMaster_View, "DefectRate1", each DIVIDE(
CALCULATE(
SUM ( [InvoiceQty] ),
FILTER (vSalesMaster,vSalesMaster[OrderType] = "W")
),
CALCULATE(
SUM ( [InvoiceQty] ),
FILTER (VSalesMaster,vSalesMaster[OrderType] = "R")
)
)
@Anonymous the token right partentheses error means that your left parentheses and right parenthesis do not match up. So you seem to have more ( then ) in your formula. Your formula seems to have 8 * ( and 7 * ). But I think you had figured this one out already.
As for your results of your formula: I am not sure that SUM is the right calculator. I think you should be using COUNT.
If I read your message correctly, your table has a row for each order na each order is either a "W" Waranty or a "R" Regular order.
Since you are summing the invoice quantity, I am wondering if your source data is correct. To me it seems like the waranty lines might contain incorrect InvoiceQTY data for your purpose.
@Anonymous
Don't do this in Query Editor.
Close that Window and to Main Screen.
Let me know if need more help
Victor
Thank you again. I am now getting numbers but they seem off. The defect rate is between 120,000% and 150,000%.
For Example: January should be 6,511/100,310 = 6.49% but the chart shows 123,209%
Hi @Anonymous,
Please share your sample data for further analysis. If possible, please upload your pbix file.
Regards,
Yuliana Gu
@Anonymous
Hi, is very difficult find a reason not viewing the data.
Try to create a measure to only sum the R and other to only sum the W and show in a visuals with the months, Maybe this give you a clue to solve it.
Thank you all very much for the responses. I reloaded my two tables in a new file and merged them together. The formula works great now. I appreciate the assistance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |