Forum Discussion
UCL calculations limits and graph
Hello,
I am new to powerBi and I need to create a upper control limit that is generated base on the las 15 months data using 2 sigmas (UCL= overall average + 2sigma). The data I have is from different product and some mothns I dont record any values (see image below). I want to use a slicer to be able to differenciate products.
Hi KOLD1313 ,
Based on the testing, please try the following methods:
1.Create the sample table.
2.Create the new measure to calculate average.
Average Rate = CALCULATE( AVERAGE('Table'[Rejects rate]), DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -15, MONTH) )3.Create the new measure to calculate the std.
Std Rate = CALCULATE( STDEV.P('Table'[Rejects rate]), DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -15, MONTH) )4.Create the measure to calculate the ucl.
UCL Rate = [Average Rate] + 2 * [Std Rate]5.The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi KOLD1313
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
Hi KOLD1313 ,
Based on the testing, please try the following methods:
1.Create the sample table.
2.Create the new measure to calculate average.
Average Rate = CALCULATE( AVERAGE('Table'[Rejects rate]), DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -15, MONTH) )3.Create the new measure to calculate the std.
Std Rate = CALCULATE( STDEV.P('Table'[Rejects rate]), DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -15, MONTH) )4.Create the measure to calculate the ucl.
UCL Rate = [Average Rate] + 2 * [Std Rate]5.The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
