How to count the number of questions that have the average score below 3.0

Question

Here is the table example. The decimal values are the averages. On the left are the course titles in one column. Across the matrix are the questions as string. The values are the averages which I'm trying to check which question has the value below 3.0 and count the number of questions which has the average value below 3.0 and show in one column.

In the screen shot below the question 2 for course 3 has average below 3.0 and also for course 2 the question 4 has value below 3.0 and I would like to count those questions and display in one column. Any ideas?

I also tried this but it only shows 1's in the last column instead of counting or summing all those question averages below 3.0. Any ideas on this? Test Measure = if([Question 1 Measure] < 3, 1, 0 + if([Question 2 Measure] < 3, 1, 0 + if( [Question 3 Measure] < 3, 1, 0 + if( [Question 4 Measure] < 3, 1, 0 + if([Question 5 Measure] < 3, 1, 0 + if([Question 6 Measure] < 3, 1, 0 + if([Question 7 Measure] < 3, 1, 0 + if( [Question 8 Measure] < 3, 1, 0 + if([Question 9 Measure] < 3, 1, 0 + if([Question 10 Measure] < 3, 1, 0 + if( [Question 11 Measure] < 3, 1, 0 + if( [Question 12 Measure] < 3, 1, 0 + if([Question 13 Measure] < 3, 1, 0 + if( [Question 14 Measure] < 3, 1, 0 + if([Question 15 Measure] < 3, 1, 0 + if( [Question 16 Measure] < 3, 1, 0 + if([Question 17 Measure] < 3, 1, 0 + if([Question 18 Measure] < 3, 1 , 0 + if( [Question 19 Measure] < 3, 1 ,0)))))))))))))))))))

Answer 1

You can calculate the number of questions with an average score < 3 as follows:

Number of Questions With Scores < 3 = CALCULATE(COUNT('Table'[Question]), FILTER('Table', [Average Score] < 3))

However, you cannot add this as a separate column at the end of a matrix. If you have Average Score as the values in a matrix with Questions as the column headers and Courses as the rows, and you add this new measure to the values, you will get a column with this new measure for each question, and a total at the end:

To achieve the visual you are looking for, you would need individual average measures for each question; this would be very tedious if you have a lot of questions:

Question 1 Average Score = CALCULATE([Average Score], FILTER('Table', 'Table'[Question] = "Question 1"))

You will need to add this measure for each question by right clicking on the table you wish to add the measure to and selecting New measure:

Then, you could include each of the Question # Average Score measures and the Number of Questions with Scores < 3 as the values and get something like this: