How to calculate the production yield trend by date

[york]

Hi Experts:

   I have a raw data like below table , I want to check the production yield by Date, the yield is total PASS item divide the total item, then I want to get a chart like below line chart, it will show the yield trend by Month/Year/Week, and the different inspector will be shown by different colo. Also if there isn't any inspection during the Month/Week, the line chart will be shown nothing in that Month/Week and it can't be shown as 0% or 100%.

   Could you help me with the requirement? Thank you very much!!!

Inspector	Date	Result
A	2022/10/1	PASS
A	2022/11/1	PASS
A	2022/12/1	FAIL
B	2022/11/4	FAIL
B	2022/11/5	PASS
B	2022/10/1	FAIL
B	2022/11/1	FAIL
C	2022/12/1	PASS
C	2022/11/9	PASS
C	2022/11/10	FAIL
D	2022/11/11	PASS
D	2022/10/1	PASS

[PaulDBrown]

See if this works. First, the model:

The measure:

Yield =
VAR _Pass =
    CALCULATE (
        COUNT ( fTable[Result] ),
        FILTER ( 'Result table', 'Result table'[Result] = "PASS" )
    )
VAR _ALL =
    COUNT ( fTable[Result] )
RETURN
    DIVIDE ( _Pass, _ALL )

Set up the line visual using the fields from the dimension tables and the measure.

 Click on the dropdown for "WeekNum" and select "Show items with no data"

And you will get:

Sample PBIX file attached

[york]

Hi Paul:

  It worked!!!! Thank you very much, but I have a question, there is a inspector and his yield is 0% from 2022/10 to 2022/11, why the line chart doesn't show 0% when I count the total yield by inspector? And there isn't the inspector's line in the line chart shown by Month/Week?

[PaulDBrown · Accepted solution]

I see what you mean. Change the measure to:

Yield =
VAR _Pass =
    CALCULATE (
        COUNT ( fTable[Result] ),
        FILTER ( 'Result table', 'Result table'[Result] = "PASS" )
    )
VAR _ALL =
    COUNT ( fTable[Result] )
RETURN
    IF ( AND ( ISBLANK ( _Pass ), _ALL > 0 ), 0, DIVIDE ( _Pass, _ALL ) )

What do you mean by "there isn't the inspector's line in the line chart shown by Month/Week"?

[york]

Hi Paul:

  It's really nice of you!!!!! All of my questions have been solved!!!😉