cancel
Showing results for
Did you mean:
javedbh 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
Highlighted
javedbh Regular Visitor

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

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.

3 REPLIES 3
v-yulgu-msft 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.  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 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
)
) 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

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

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

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.

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 228 members 2,296 guests
Recent signins:
• emmetk • Shawn_Fitz • jeffatkins79 • PaulDBrown • MrHankey • v-leoca • ddguedes • nielsentm • jomagarcia • JParker66 • Sadiq9349 • Ali-Aati • remcodegroot • shilparajesh 