cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Solution Sage

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

Solution Sage

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors