Forum Discussion
Quality Indicators Monthly Percentage
Hello,
I am maintaining quality indicator data in the following format in MS Excel:
| Month | Indicator | Pt. Completed <= 30 min | Pt. Completed <= 90 min. | Total no. of OP | surgery accep. Pts. within 12 weeks | Surgery advised pts. Within 12 weeks |
| Oct-16 | Initial Assessment Time | 13449 | 0 | 20706 | 0 | 0 |
| Oct-16 | No of patient seen <90 min. | 0 | 14233 | 0 | 0 | 0 |
| Oct-16 | Waiting time for cataract surgery | 0 | 0 | 0 | 2266 | 3094 |
| Oct-16 | Bed Occupancy rate | 0 | 0 | 0 | 0 | 0 |
| Oct-16 | ALOS | 0 | 0 | 0 | 0 | 0 |
| Oct-16 | Rescheduled surgeries | 0 | 0 | 0 | 0 | 0 |
| Oct-16 | No. of reporting errors (Lab) | 0 | 0 | 0 | 0 | 0 |
| Oct-16 | No. of re dos (Lab) | 0 | 0 | 0 | 0 | 0 |
| Nov-16 | Initial Assessment Time | 13330 | 0 | 21647 | 0 | 0 |
| Nov-16 | No of patient seen <90 min. | 0 | 15012 | 0 | 0 | 0 |
| Nov-16 | Waiting time for cataract surgery | 0 | 0 | 0 | 2551 | 3498 |
| Nov-16 | Bed Occupancy rate | 0 | 0 | 0 | 0 | 0 |
| Nov-16 | ALOS | 0 | 0 | 0 | 0 | 0 |
| Nov-16 | Rescheduled surgeries | 0 | 0 | 0 | 0 | 0 |
| Nov-16 | No. of reporting errors (Lab) | 0 | 0 | 0 | 0 | 0 |
| Nov-16 | No. of re dos (Lab) | 0 | 0 | 0 | 0 | 0 |
I wish to calculate the monthly % of each quality indicator, as follows:
Initial Assessment Time = Pt. completed <= 30 min. / Total no. of OP
[For Oct 2016 it works to be 13449/20706 = 65%]
[For Nov 2016 it works to be 13330/21647 = 62%]
Within 90 Min = Pt. completed <= 90 min. / Total no. of OP
[For Oct 2016 it works to be 14233/20706 = 69%]
[For Nov 2016 it works to be 15012/21647 = 69%]
So on and so forth for all months and other indicators as well. After this, it has to be plotted on a combo chart as follows (The chart below is indicative and not matching to actual calculation above):
How do I calculate these percentages?
The unpivoted data in Power BI looks like:
Thanks in Advance.
Before you unpivot columns in Power BI Desktop query editor, you can replace 0 values in Total no. of OP column with null, then fill down.
Then create the following measures in your table.% Pt. Completed<30 = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Pt. Completed <= 30 min")),CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% Pt. Completed<90 = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Pt. Completed <= 90 min.")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% surgery accep. Pts. within 12 weeks = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="surgery accep. Pts. within 12 weeks")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% Surgery advised pts. Within 12 weeks = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Surgery advised pts. Within 12 weeks")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
However, it is not possible to put all the measures in the above chart as you describe, as the measures will be calculated to return different results based on the fields in the chart, you can create a table visual to view the results instead.
Regards,Lydia
3 Replies
Before you unpivot columns in Power BI Desktop query editor, you can replace 0 values in Total no. of OP column with null, then fill down.
Then create the following measures in your table.% Pt. Completed<30 = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Pt. Completed <= 30 min")),CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% Pt. Completed<90 = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Pt. Completed <= 90 min.")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% surgery accep. Pts. within 12 weeks = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="surgery accep. Pts. within 12 weeks")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
% Surgery advised pts. Within 12 weeks = DIVIDE(CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Surgery advised pts. Within 12 weeks")), CALCULATE(SUM(Table1[Nos]),FILTER(Table1,Table1[Details]="Total no. of OP")),0)
However, it is not possible to put all the measures in the above chart as you describe, as the measures will be calculated to return different results based on the fields in the chart, you can create a table visual to view the results instead.
Regards,Lydia
