Upper Control Limit in Line Chart with Small Multiple (Wards)

[awsiya]

I am facing challenges with setting an Upper Control Limit (UCL) in my PowerBI Desktop Line chart. My data source is a simple Excel sheet, and I a,m using StartofMonth on the X-axis, Sum of Incidents/1000BedDays on the Y-axis, and Ward in SMALL Multiple. I have two categories: Falls and Injury, and I intend to create two line charts—one for Falls displaying the Falls' Upper Control Limit and one for Injury showing the Injury Upper Control Limit for each ward. However, the UCL I am obtaining is static (I believe it's the average of all wards and not showing the UCL per ward). How can I resolve this issue? I am using. I am using Quartile 1 & 3 way for Limit

Quartile 1 = PERCENTILE.INC(UCL[Incident per 1000 beddays], 0.25)

Quartile 3 = PERCENTILE.INC(UCL[Incident per 1000 beddays], 0.75)

IQR = [Quartile 3]-[Quartile 1]

UCLimit = CALCULATE([Quartile 3] + 1.5 * ([IQR]), ALL())

data set is below: I dont mind if there is any other measure to get the UCLimit like St Deviation method etc as long as I get what I want

WardDesc	StartofMonth	Incident per 1000 beddays	Fall_PI.Incident type tier one
Ward A	01/01/2024	5.148005148	Falls
Ward A	01/01/2024	2.574002574	Injury
Ward A	01/02/2024	9.446693657	Falls
Ward A	01/02/2024	2.699055331	Injury
Ward A	01/03/2024	12.72264631	Falls
Ward A	01/03/2024	1.272264631	Injury
Ward A	01/04/2024	8.174386921	Falls
Ward A	01/05/2024	12.34567901	Falls
Ward A	01/05/2024	3.086419753	Injury
Ward B	01/04/2024	1.043841336	Falls
Ward C	01/01/2024	14.17004049	Injury
Ward C	01/02/2024	3.64298725	Injury
Ward C	01/03/2024	1.658374793	Falls
Ward C	01/03/2024	18.24212272	Injury
Ward C	01/04/2024	5.366726297	Injury
Ward C	01/05/2024	29.41176471	Injury
Ward D	01/01/2024	2.491694352	Falls
Ward D	01/02/2024	0.769230769	Falls
Ward D	01/03/2024	3.295978906	Falls
Ward D	01/04/2024	5.029337804	Falls
Ward D	01/05/2024	1.751313485	Falls
Ward D	01/05/2024	1.751313485	Injury
Ward E	01/01/2024	1.919385797	Falls
Ward E	01/01/2024	1.919385797	Injury
Ward E	01/02/2024	1.992031873	Falls
Ward E	01/04/2024	2.127659574	Falls
Ward E	01/05/2024	5.128205128	Injury
Ward F	01/01/2024	7.594936709	Injury
Ward F	01/02/2024	12.90322581	Injury
Ward F	01/03/2024	16.2601626	Injury
Ward F	01/04/2024	7.159904535	Injury
Ward F	01/05/2024	14.28571429	Injury
Ward H	01/01/2024	4.651162791	Falls
Ward H	01/02/2024	4.694835681	Falls
Ward H	01/03/2024	6.802721088	Falls
Ward H	01/04/2024	7.444168734	Falls
Ward H	01/05/2024	7.142857143	Falls
Ward I	01/01/2024	4.47761194	Falls
Ward I	01/01/2024	1.492537313	Injury
Ward I	01/02/2024	6.088280061	Falls
Ward I	01/03/2024	4.322766571	Falls
Ward I	01/03/2024	1.44092219	Injury
Ward I	01/04/2024	1.47275405	Falls
Ward I	01/05/2024	3.597122302	Falls
Ward I	01/05/2024	3.597122302	Injury
Ward J	01/01/2024	2.478314746	Falls
Ward J	01/03/2024	2.298850575	Falls
Ward J	01/03/2024	3.448275862	Injury
Ward J	01/04/2024	2.322880372	Falls
Ward J	01/05/2024	6.369426752	Falls
Ward K	01/02/2024	2.762430939	Injury
Ward K	01/03/2024	3.271537623	Injury
Ward K	01/04/2024	3.562945368	Injury
Ward L	01/01/2024	15.87301587	Injury
Ward L	01/02/2024	10.57082452	Injury
Ward L	01/03/2024	7.936507937	Injury
Ward L	01/04/2024	11.9047619	Injury
Ward L	01/05/2024	8.771929825	Injury

[Anonymous · Accepted solution]

Hi, @awsiya 

First, create a measure and calculate the average incidents per 1000 bed days for each ward.

AvgIncidentsPerWard = AVERAGE('Table'[Incident per 1000 beddays])

 Next, create new measure and calculate the standard deviation for incidents per 1000 bed days for each ward.

StdDevPerWard = STDEV.P('Table'[Incident per 1000 beddays])

Then calculate the UCL for each ward. You can adjust the multiplier (in this case, 1.5) based on your UCL requirements.

UCLPerWard = [AvgIncidentsPerWard] + (1.5 * [StdDevPerWard])

 Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.