Calculate the Top 3 Columns with the Most Instances of One Word

[Leeny127]

I have a table that shows which questions on a test were answered correctly by student (No = incorrect, Yes = correct)

Student       Question-A     Question-B     Question-C     Question-D     Question-E     Question-F

   Joe                  No                   Yes                   Yes                   Yes                    No                  Yes

   Ian                   No                   Yes                   Yes                   Yes                    No                  Yes      

   Tia                   No                    No                   Yes                   Yes                    Yes                  Yes

What I would like to do is: 

1) Calculate the top 3 questions with the most "No" answers and display them in a visual

2) Show the percentage of all "Nos" in the table that question accounts for

For example: the following questions would show up in my visual (multi row card perhaps), in order of highest "No" count

Question-A    17%

Question-E     11%

Question-B     6%

Any guidance is much appreciated!

[Interkoubess · Accepted solution]

Hi @Leeny127,

Please modify your data with Power Query by unpivoting all the anwer and keeping the student field.

Then load it in the power pivot model and you can use this formula where I called my table(Table1), and the 3 fields Student, Answer and Question:

Measure = var AnswerNo=CALCULATE(COUNT(Table1[Answer]),Table1[Answer]="No") var AnswerAl=CALCULATE(COUNT(Table1[Answer]), ALL(Table1[Question])) return DIVIDE(AnswerNo,AnswerAl,BLANK())

Please feel free to ask it it does not work because it works on my side.

Ninter