I know how I would handle this in SQL, but I'm being asked to do this in an Excel Power Query/Power Pivot, with which I am less familiar. I have medical data that looks something like this:

Doctor	Patient	Eligible for cervical screening	Received cervical screening	Eligible for colorectal screening	Received colorectal screening
Doctor1	Pat1	Yes	Yes	Yes	Yes
Doctor1	Pat2	Yes	Yes	Yes	No
Doctor1	Pat3	Yes	No	Yes	Yes
Doctor1	Pat4	No	No	Yes	Yes
Doctor1	Pat5	No	No	Yes	No
Doctor1	Pat6	No	No	No	No

I know how I would handle this in SQL, but I'm being asked to do this in an Excel Power Query/Power Pivot, with which I am less familiar. I have medical data that looks something like this:

Doctor	Patient	Eligible for cervical screening	Received cervical screening	Eligible for colorectal screening	Received colorectal screening
Doctor1	Pat1	Yes	Yes	Yes	Yes
Doctor1	Pat2	Yes	Yes	Yes	No
Doctor1	Pat3	Yes	No	Yes	Yes
Doctor1	Pat4	No	No	Yes	Yes
Doctor1	Pat5	No	No	Yes	No
Doctor1	Pat6	No	No	No	No

We also know that the goal for cervical screenings performed is at least 70%, and for colorectal screenings is at least 79%. This percentage is number performed over number eligible.

In Excel, we've been able to create a Pivot Table that looks like:

Doctor	Measure	Eligible	Performed	Goal	# Needed to Goal
Doctor 1	Cervical	3	2	70%	1
Doctor 1	Colorectal	5	3	79%	1
Doctor 1 Total		8	5		0

The formula for the # Needed to Goal measure is =ROUND(IF((MIN([Goal])/100 * [Sum of Eligible]) - [Sum of Received]<= 0, 0, (MIN([Goal])/100 * [Sum of Eligible]) - [Sum of Received]),0). The problem is that doctor total under #Needed to Goal in that lower right column. They want it to be 2, as in the sum of the #Needed to Goal from the cervical and colorectal lines. I can't figure out how to sum that measure in the subtotal row. I couldn't find an answer in Google, but I may not have been searching the right terms. Could anyone point me in the right direction?

• excel
• pivot-table
• measure

Share Improve this question Follow edited Nov 19, 2024 at 23:19 Michal 6,17444 gold badges2626 silver badges4343 bronze badges asked Nov 19, 2024 at 22:23 Kim LloydKim Lloyd 1322 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

In Power Query you can obtain your results table from your data with this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Doctor", type text}, {"Patient", type text}, {"Eligible for cervical screening", type text}, {"Received cervical screening", type text}, {"Eligible for colorectal screening", type text}, {"Received colorectal screening", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Doctor", "Patient"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Measure", each Text.Proper(List.LastN(Text.Split([Attribute]," "),2){0}), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Doctor","Measure"}, {
        {"Eligible", (t)=>Table.RowCount(Table.SelectRows(t, each Text.Contains([Attribute],"Eligible") and [Value]= "Yes")), Int64.Type},
        {"Performed", (t)=>Table.RowCount(Table.SelectRows(t, each Text.Contains([Attribute],"Received") and [Value]= "Yes")), Int64.Type}}),

//Could have goals in a lookup table
// or enter them as Parameters
// and then alter the IF statement
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Goal", each 
        if [Measure] = "Cervical" then 0.7
        else if [Measure] = "Colorectal" then 0.79
        else null, 
        Percentage.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "# Needed to Goal", 
        each Number.RoundAwayFromZero([Eligible] * [Goal] - [Performed]),Int64.Type)
in
    #"Added Custom2"

You can then add the totals in the resultant table in Excel

• Select the Table
• Table Design => Totals Row

If you update the table, with the proper options, the totals row will also update.

You can also add a totals row within the query. That is more complex but doable. Let me know if you prefer that method.