Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |