Return top N and bottom N columns from a group

[Anonymous]

So I have a dataset with info of patients that is updated daily. One of the things I have to measure is a percentage of completion of some charts with information. In Excel they're divided into sections with colors, but I can't use that in Power BI.

The rules are:

• If any or all of a section's entries are blank, the person's chart is considered not audited for the day and is not taken into account for the daily average (It is saved as null).
• All entries must be binary (1 or 0)
• For every section, make a percentage of completion (1s/amount of columns)

I already have the percentages in hand, however, what is also required to know is what columns were left empty, and their top 3 per section. I figured a waterfall visual would be best for the task, but when you have all the columns listed and you have gaps here and there, it's not very visually appealing.

I'd also like to know how to do this on a matrix for a column with Dr. names. Who were the top 3 at filling charts in a day, and who were the bottom 3. (detailing their performance at every section)

To filtrer to a daily date, I'm using a slider with A calendar Date to today, and it has a relationship to a date column within my data ([Revision Date]), and on my CALCULATE() filters, I use 

FILTER(HP-CHARTING, HP-CHARTING[Revision Date] == MAX(HP-CHARTING[Revision Date]))

I also must note that I tried using Group By to group every section together, but I end up making tables within that group, and that's not what I'm looking for.

Here's a sample of my data, if anyone could help me go the right direction I'd really appreciate it.

(p.s. I replaced the names on the binary colums to separate and identify, but that's not their actual name, they all have different names.)

[Anonymous]

Hi @lbendlin, thanks for stopping by. Unfortunately, that's not what I'm asking for help with...

I'm already done with that part of my data, I have calculated the percentages based on the rules I listed, and made a simple waterfall graphic listing all the colums each category contains. Do note they're the actual name of the columns and not the ones I procedurally made on the CSV for example purposes.

What I'm asking about is how to make it so I don't have to list all my columns on the waterfall graph to see how my values are. I'd like to make it so only the top 3 with values are listed. Same with Dr. Names. I currently have a matrix going on but it's completely ignoring the date slider and I don't understand why. 

[lbendlin · Accepted solution]

As you may already have noticed there is no concept in Power BI to dynamically address columns. Hence my proposal to unpivot your data so you can operate on rows instead, and calculate your Top 3 / Bottom 3 from there.

[lbendlin]

Should NAs be treated as zeros or as null ?

Here is the process to bring your data into a usable format.  After that you can use PRODUCTX  or similar to calculate the pass rate per section.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\HP-CHARTING.xlsx"), null, true),
    in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(in_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID", "Revision Date", "Admission Date", "Area", "Episode Number", "Patient Number", "Patient Name", "Dr Name"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","NA",0,Replacer.ReplaceValue,{"Value"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Section", "Column"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Int64.Type}})
in
    #"Changed Type1"