Reply
Regular Visitor
Posts: 41
Registered: ‎04-26-2016
Accepted Solution

Calculate standard deviation on the yield of 5 previous lots

Hi All,

 

I want to calculate standard deviation on the yield of 5 previous lots.

 

Here is the data:

Device

Lot_ID

Pass_Dies

Total_Dies

Yield

Std.Dev.

Device1

Lot1

50

100

50.00

?

Device1

Lot2

60

120

50.00

?

Device1

Lot3

80

100

80.00

?

Device1

Lot4

60

80

75.00

?

Device2

Lot5

55

80

68.75

?

Device2

Lot6

80

150

53.33

?

Device2

Lot7

81

110

73.64

?

Device2

Lot8

95

140

67.86

?

Device2

Lot9

75

100

75.00

?

Device2

Lot10

43

70

61.43

?

 

 

I can calculate yield using following measure:

Total Dies = SUM(Table1[Total_Dies])

Pass Dies = SUM(Table1[Pass_Dies])

Yield = DIVIDE([Pass Dies], [Total Dies], 0) * 100

Std Dev = ?

 

For every lot, take yield of previous 5 lots and calculate standard deviation.

 

Anybody how to achieve this?

 

 


Accepted Solutions
Highlighted
Regular Visitor
Posts: 41
Registered: ‎04-26-2016

Re: Calculate standard deviation on the yield of 5 previous lots

Hi @v-yulgu-msft

 

I added an index column and use your instruction and it worked. 

 

Here is the updated list of columns:

Device, Program, Lot_Id, Lot_Finish_Date, Total_Dies, Pass_Dies

 

I have a line chart where Lot_ID is on x-axis. On y-axis, there are two measures, 1) yield, 2) std.dev. of yield. I also have a couple of slicers e.g. year, quarter, device etc.

 

Now the problem is that if I apply different filters on the data either throuh visual/page filters or slicers, then Std. Dev. does not always update itself correctly because index column has fixed values.

 

Is it possible that index (or rank) column works in such a way that it updates itself (i.e. start from 1) whenever filters/slicers are used so that std.dev. measure is also more dynamic.

 

Can we use Lot_Finish_Date column in a measure to get yield of last 5 lots and calculate std.dev. e.g. get yield of latest 5 lots where Lot_Finish_Date <= This_Lot_Finish_Date and calculate std.dev.

View solution in original post


All Replies
Community Support Team
Posts: 5,647
Registered: ‎09-21-2016

Re: Calculate standard deviation on the yield of 5 previous lots

Hi @javedbh,

 

In Query Editor mode, duplicate [Lot_ID] column, then, split the duplicated column in order to get the ID number.

1.PNG

2.PNG

 

In data view mode, rather than creating a measure, please create a calculated column to get the [Yield] values.

Yield Col = (Table1[Pass_Dies]/Table1[Total_Dies])*100

3.PNG

 

Refer to below measure to calculate the standard deviation.

Std.Dev =
CALCULATE (
    STDEV.P ( Table1[Yield Col] ),
    FILTER (
        ALL ( Table1 ),
        Table1[rank ID] <= MAX ( Table1[rank ID] )
            && Table1[rank ID]
                >= MAX ( Table1[rank ID] ) - 4
    )
)

4.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 41
Registered: ‎04-26-2016

Re: Calculate standard deviation on the yield of 5 previous lots

Thanks @v-yulgu-msft

 

Values in Lot_ID column are only for reference. Actual values differ. However I can add an index column and use it in the standard deviation measure. I will try it and let you know.

Highlighted
Regular Visitor
Posts: 41
Registered: ‎04-26-2016

Re: Calculate standard deviation on the yield of 5 previous lots

Hi @v-yulgu-msft

 

I added an index column and use your instruction and it worked. 

 

Here is the updated list of columns:

Device, Program, Lot_Id, Lot_Finish_Date, Total_Dies, Pass_Dies

 

I have a line chart where Lot_ID is on x-axis. On y-axis, there are two measures, 1) yield, 2) std.dev. of yield. I also have a couple of slicers e.g. year, quarter, device etc.

 

Now the problem is that if I apply different filters on the data either throuh visual/page filters or slicers, then Std. Dev. does not always update itself correctly because index column has fixed values.

 

Is it possible that index (or rank) column works in such a way that it updates itself (i.e. start from 1) whenever filters/slicers are used so that std.dev. measure is also more dynamic.

 

Can we use Lot_Finish_Date column in a measure to get yield of last 5 lots and calculate std.dev. e.g. get yield of latest 5 lots where Lot_Finish_Date <= This_Lot_Finish_Date and calculate std.dev.