Forum Discussion

awsiya's avatar
awsiya
Helper I
2 years ago
Solved

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

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
WardDescStartofMonthIncident per 1000 beddaysFall_PI.Incident type tier one
Ward A01/01/20245.148005148Falls
Ward A01/01/20242.574002574Injury
Ward A01/02/20249.446693657Falls
Ward A01/02/20242.699055331Injury
Ward A01/03/202412.72264631Falls
Ward A01/03/20241.272264631Injury
Ward A01/04/20248.174386921Falls
Ward A01/05/202412.34567901Falls
Ward A01/05/20243.086419753Injury
Ward B01/04/20241.043841336Falls
Ward C01/01/202414.17004049Injury
Ward C01/02/20243.64298725Injury
Ward C01/03/20241.658374793Falls
Ward C01/03/202418.24212272Injury
Ward C01/04/20245.366726297Injury
Ward C01/05/202429.41176471Injury
Ward D01/01/20242.491694352Falls
Ward D01/02/20240.769230769Falls
Ward D01/03/20243.295978906Falls
Ward D01/04/20245.029337804Falls
Ward D01/05/20241.751313485Falls
Ward D01/05/20241.751313485Injury
Ward E01/01/20241.919385797Falls
Ward E01/01/20241.919385797Injury
Ward E01/02/20241.992031873Falls
Ward E01/04/20242.127659574Falls
Ward E01/05/20245.128205128Injury
Ward F01/01/20247.594936709Injury
Ward F01/02/202412.90322581Injury
Ward F01/03/202416.2601626Injury
Ward F01/04/20247.159904535Injury
Ward F01/05/202414.28571429Injury
Ward H01/01/20244.651162791Falls
Ward H01/02/20244.694835681Falls
Ward H01/03/20246.802721088Falls
Ward H01/04/20247.444168734Falls
Ward H01/05/20247.142857143Falls
Ward I01/01/20244.47761194Falls
Ward I01/01/20241.492537313Injury
Ward I01/02/20246.088280061Falls
Ward I01/03/20244.322766571Falls
Ward I01/03/20241.44092219Injury
Ward I01/04/20241.47275405Falls
Ward I01/05/20243.597122302Falls
Ward I01/05/20243.597122302Injury
Ward J01/01/20242.478314746Falls
Ward J01/03/20242.298850575Falls
Ward J01/03/20243.448275862Injury
Ward J01/04/20242.322880372Falls
Ward J01/05/20246.369426752Falls
Ward K01/02/20242.762430939Injury
Ward K01/03/20243.271537623Injury
Ward K01/04/20243.562945368Injury
Ward L01/01/202415.87301587Injury
Ward L01/02/202410.57082452Injury
Ward L01/03/20247.936507937Injury
Ward L01/04/202411.9047619Injury
Ward L01/05/20248.771929825Injury


 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.