Forum Discussion
Production Yield calculation
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 |
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
1 Reply
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