Search code examples
powerbidax

How to create a Power Bi measure to calculate First Pass Yield based off unique serial numbers

I need to create a power bi measure that calculates First Pass Yield (FPY) based on unique serial numbers.

Measure (FPY) = 
(Count of unique serial numbers that dont have a "Fail" result) 
divided by 
(Count of total amount of unique serial numbers)

This measure could then be plotted on a chart that could be dynamically filtered by a date range slicer:

enter image description here

Solution

  • This should do the trick:

    FPY =
VAR FailedSerialNumbers =
    CALCULATETABLE (
        VALUES ( 'Table'[Serial Number] ),
        FILTER ( ALL ( 'Table'), 'Table'[RESULT] = "FAIL" )
    )
VAR SerialNumbers =
    VALUES ( 'Table'[Serial Number] )
VAR FPYResult =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Serial Number] ),
        'Table'[Serial Number] IN EXCEPT ( SerialNumbers, FailedSerialNumbers )
    )
VAR SerialNumberVolume =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Serial Number] ) )
RETURN
    DIVIDE ( FPYResult, SerialNumberVolume )

    Which yields the following:

    enter image description here

    This solution uses two table variables, 1 with your failed serial numbers and another with all serial numbers. Using these tables, I can extract all serial numbers that have never failed using the EXCEPT function. Once this value has been calculated we just divide it by the total volume.

    I hope it helps!