Quality Indicators Monthly Percentage

[deepvibha]

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.

[v-yuezhe-msft · Accepted solution]

@deepvibha,

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

[deepvibha]

@v-yuezhe-msft

Heartfelt thanks for the solution. I was struggling for some time now as I wanted to find a solution myself.

Regards,

Deepak

[deepvibha]

@v-yuezhe-msft

Heartfelt thanks for the solution. I was struggling for some time now as I wanted to find a solution myself.

Regards,

Deepak