Forum Discussion

macemit's avatar
macemit
Frequent Visitor
7 years ago

Failure rate running total based on counted fields

Hi,

 

I'm trying to calculate a running total of a count of product failures over a count of products shipped:

 

 Count of FailuresCount of ShippedWrongCorrectMethodology
June11001.0%1.0%1/100
July51005.0%3.0%6/200
August1510015.0%7.0%21/300
September1010010.0%7.8%31/400

 

When I create a measure: Failure rate = COUNTA('Case_Serial_Number'[Failure_Type]) / COUNTA('Serial_Numbers'[Serial]) - I get the failure rate for that month (the wrong column above). The calculation needs to take into account the specific months failures and shipped quantities and all the failures and shipped that preceded it. Thanks.

Need Help

3 Replies

  • Interkoubess's avatar
    Interkoubess
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi macemit,

     

    I changed the field names ( to Shipped and Failed) and table name ( Table) and I created a date ( first date of every month) and finally created this measure:

    Per = var CounFail = CALCULATE(SUM('Table'[ Failures]),FILTER(ALL('Table'[Date]),'Table'[Date]<=MAX('Table'[Date]))) var CounShip = CALCULATE(SUM('Table'[Shipped]),FILTER(ALL('Table'[Date]),'Table'[Date]<=MAX('Table'[Date]))) return DIVIDE(CounFail,CounShip,BLANK())

     

     

     

    Hope it helps....

     

    Ninter

    • macemit's avatar
      macemit
      Frequent Visitor
      7 years ago

      Thanks for that. I was away from the office for a couple days hence the delay responding. Question: can I use counta instead of sum, as the fields are text and cannot be summed.

       

      And 1 issue: serial number and failures came from 2 different tables. Is this how your measure is calculating? I'm getting a 'syntax for 'return' is incorrect'

       

      Thanks.