Defect Rate Formula

[Anonymous]

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. 

[Vvelarde · Accepted 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]

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")
)
)

[Anonymous]

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")
)
)

[DcAssink]

@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.

[Vvelarde]

@Anonymous

Don't do this in Query Editor.

Close that Window and to Main Screen.

Let me know if need more help

Victor

[Anonymous]

@Vvelarde

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%

[v-yulgu-msft]

Hi @Anonymous,

Please share your sample data for further analysis. If possible, please upload your pbix file.

Regards,
Yuliana Gu

[Vvelarde]

@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.

[Anonymous]

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.