Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Leeny127
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
Interkoubess
Solution Sage
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.

 

NinterTestNo.PNG

View solution in original post

1 REPLY 1
Interkoubess
Solution Sage
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.

 

NinterTestNo.PNG

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.