Production Yield calculation

[vincentakatoh]

Hi, 

Trying to use BI to analyse Yield information of production. Sample data at the botom. I beleived below is useful to anyone using BI to analyse yield information. 

From previous post, learned to 1) duplicate data , 2) pivot column and c) index column. Generate below matrix. 

Questions

1) Line Yield DAX formula to calculate line yield rate (station yield = P/(P+F))

Line 1 yield = S1 yield * S2 yield = [2/(1+2)] * [1/(1+2)] = 22% <exclude stations with zero P and zero F>

Line 3 yield = S1 yield * S2 yield * S3 yield = [0/(0+3)] * [0/(0+2)] *[ 0/(0+1)] = 0% 

Line 4 yield = S1 yield * S3 yield * S4 yield = [2/(1+2)] * [1/(1+0)] * [2/2+0] = 67%

2) Area Yield DAX formula to calculate Area yield

Area yield = S1 yield * S2 yield * S3 yield * S4 yield = [6/(6+6)] * [2/(2+6)] *[ 1/(1+1)]*[ 2/(2+0)]

PS. Actual data has >50 stations (S1, S2, S3.... S50)

Area	Line	station	test results
SMT	Line1	S1	P
SMT	Line1	S1	F
SMT	Line1	S1	P
SMT	Line1	S2	F
SMT	Line1	S2	F
SMT	Line1	S2	P
SMT	Line2	S1	P
SMT	Line2	S1	F
SMT	Line2	S1	P
SMT	Line2	S2	F
SMT	Line2	S2	F
SMT	Line2	S2	P
SMT	Line3	S1	F
SMT	Line3	S1	F
SMT	Line3	S1	F
SMT	Line3	S2	F
SMT	Line3	S2	F
SMT	Line3	S3	F
SMT	Line4	S1	P
SMT	Line4	S1	F
SMT	Line4	S1	P
SMT	Line4	S4	P
SMT	Line4	S4	P
SMT	Line4	S3	P

[v-qiuyu-msft · Accepted solution]

Hi @vincentakatoh,

Firstly, it's not supported to add a column on the most right of the matrix visual using a measure. Also there is no function to support recursive calculation. In your scenario, you need to hardcode S1 yield or S2 yield. For example, Line 1 yield = S1 yield * S2 yield = [2/(1+2)] * [1/(1+2)] = 22%. You can create the measure like below:

M1 = var P=LOOKUPVALUE('Table'[CountVal],'Table'[Line],MAX('Table'[Line]),'Table'[station],MAX('Table'[station]),'Table'[test results],"P")
var PS2=LOOKUPVALUE('Table'[CountVal],'Table'[Line],MAX('Table'[Line]),'Table'[station],"S2",'Table'[test results],"P")
var FS2=LOOKUPVALUE('Table'[CountVal],'Table'[Line],MAX('Table'[Line]),'Table'[station],"S2",'Table'[test results],MAX('Table'[test results]))
Return 
IF(MAX('Table'[Line])="Line1" && MAX('Table'[station])="S1" && MAX('Table'[test results])="F",
(P/(P+SUM('Table'[CountVal])))*(PS2/(PS2+FS2)),BLANK())

Best Regards,
Qiuyun Yu