Yield Bridge Chart

[vincentakatoh]

Hi,

Trying to create a "Yield Bridge" chart. A Yield Bridge Chart is similar to a Pareto but has a different Line Values. 

Yield Bridge vs Pareto: Yield bridge shows the current yield and subsequent yield if the defects in specific station are resolved. This is particularly useful to understand what are the % improvement required to meet a specific yield target. 

Hard part of yield bridge is the starting point for Line Values is a calculated yield. Objective is to get a Yield Bridge chart that works with the "Line" Slicer. Sample data in below link.

https://1drv.ms/f/s!ArjVwEnHONXNgRlz16jjqmbL58wB

[v-qiuyu-msft]

Hi @vincentakatoh,

1. Create a calculated table:

t = SUMMARIZECOLUMNS(
        'Data'[Area],
        Data[Line],
  Data[station],
        "CountofPass",COUNTROWS(FILTer('Data',[test results]="P")),
        "CountofFail",COUNTROWS(FILTer('Data',[test results]="f")),
        "Yield",DIVIDE([CountFnPass],[CountFnTotal],0)
        )

2. Create calculated columns in this table:

StationOrder = RIGHT('t'[station],1)

YieldBri = var t1=IF(LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+1)=BLANK(),1,LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+1))
var t2= IF(LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+2)=BLANK(),1,LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+2))
var t3= IF(LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+3)=BLANK(),1,LOOKUPVALUE('t'[Yield],'t'[Area],'t'[Area],t[Line],t[Line],'t'[StationOrder],'t'[StationOrder]+3))
return
SWITCH('t'[StationOrder],1,'t'[Yield]*t1*t2*t3,2,t1*t2*t3,3,t3,4,1)

Multiply = var p1=IF(LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+1)=BLANK(),1,LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+1))
var p2=IF(LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+2)=BLANK(),1,LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+2))
var p3=IF(LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+3)=BLANK(),1,LOOKUPVALUE('t'[YieldBri],t[Line],t[Line],t[StationOrder],'t'[StationOrder]+3))
return
't'[YieldBri]*p1*p2*p3

3. Create new table:

Table = UNION(SUMMARIZE(t,'t'[Line],"YieldBri",CALCULATE(MAX('t'[Multiply]),FILTER('t','t'[station]="S1")),"Area",BLANK(),"Station",BLANK(),"CountofFail",BLANK()),SELECTCOLUMNS('t',"Line",'t'[Line],"YieldBri",'t'[YieldBri],"Area",'t'[Area],"Station",'t'[station],"CountofFail",'t'[CountofFail]))

4. Create  arelationship based on Line between Table and LinkLine.

5. Use Line and clustered column chart visual to display value from Table.

For more information, see attached .pbix file.

Best Regards,
Qiuyun Yu

[vincentakatoh]

Hi @v-qiuyu-msft

Thanks for the detailed response. it is exremely useful for a DAX dummy like me. But I have 3 more questions

1) Wrong values: Yield Bridge Line is giving the returning the wrong values (initial value 3.3% vs 5% and 2nd value 4.9% vs 22%. 

See below/attached table XLS vs PBI.

  

2) Date: Tried to add a column "Date" to "t" table, but received below error. The date column is important. 

3) Y-axis: How can i show the % in y-axis (right), Similar to the pareto. Also the last label does not show the 100%. 

[v-qiuyu-msft]

Hi @vincentakatoh,

1). We select the "Line3" in Line slicer, it's the correct values.

2). As YieldBri calculated column uses LOOKUPVALUE() function to return results, if add a 'data'[date] column in SUMMARIZED() function, it will have multiple records when search use LOOKUPVALUE() which can't be determined. As the final YieldBri multiply each row value, we require LOOKUPVALUE() function. I would suggest you don't use date to filter it.

3) It seems you want to add a secondary Y-axis for the line, you can turn on this feature:

Best Regards,
Qiuyun Yu

[vincentakatoh]

Hi @v-qiuyu-msft,

Thanks. 

(1) The PBI values are not the same as Excel values that I'm wanted to achieve. 

(2) The dates are important. Objective is to have drill down by dates and lines. Was really happy to achieve the Pareto line using 5x measures (keeping drill down functions such as dates and lines). Was hoping to do the same for Yield Bridge line. 

(3) Thanks. Got it!

[vincentakatoh]

Hi @v-qiuyu-msft,

Apologies. Notice there are formula errors in my original excel. The new formulas maybe easier to calculate. 

Appreciate your great help and patience. Really struggling to create the yield bridge chart. 

Updated excel