Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
77 | |
75 | |
62 | |
60 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |