cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
javedbh Regular Visitor
Regular Visitor

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
javedbh Regular Visitor
Regular Visitor

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

3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

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.
javedbh Regular Visitor
Regular Visitor

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.

javedbh Regular Visitor
Regular Visitor

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 207 members 2,084 guests
Please welcome our newest community members: