Forum Discussion
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)