UCL calculations limits and graph

[KOLD1313]

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.

 

[Anonymous · Accepted solution]

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.

[Ritaf1983]

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.

How to Get Your Question Answered Quickly