Forum Discussion

vincentakatoh's avatar
vincentakatoh
Helper IV
9 years ago

Yield Bridge Chart

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.
 
 
Need Help

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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's avatar
      vincentakatoh
      Helper IV
      9 years ago

      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's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        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