Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Defect Rate Formula

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's avatar
    Vvelarde
    9 years ago

    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

9 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        Anonymous

         

        Don't do this in Query Editor.

         

        Close that Window and to Main Screen.

         

         

        Let me know if need more help

         

        Victor

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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