Survey Data - Display average results below a value
Hello Everyone, I'm trying to make a simple calculation but just can't figure out the DAX formula to do it. I have a survey with 6 categories of questions. Each category has 3 to 6 questions, answered with Strongly Disagree, Disagree, Agree, Strongly Agree. I've unpivoted the questions and answers and made a conditional column to grade each response with -10, -5, 5 and 10. This is how it looks: I'm making an average of each answer per category for example in Training there are 3 questions: (10 + 10 - 5) / 3 = an average of 5 The way I display that is in a table, and I can simply ask the value to display as average or use a DAX formula like this: Average Score = Average(questions[Score]) What I'm trying to do is to see how many people have an average below 5 in each category. How I tried so far is with this formula: _No. of employees < 5 = CALCULATE(COUNTAX('Append1', [ID]),'questions'[Score]<= 5 ) But it calculates each answer individually, so it doesn't really give me the number of people, but the number of answers below or equal to 5. Later on, I want to see how many people in each group/account have a score below 5. The way I see it so far is with a table like this, but I don't want to count each red tab, in each group, but to have a direct answer. (The first column is filled with people ID)
