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:

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:

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:

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

1 Answer 1

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.