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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
OvidiuNeacsu
Resolver I
Resolver I

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: 

 

PBIDesktop_7fojFyZukj.png

 

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) 
 

 

2 REPLIES 2
OvidiuNeacsu
Resolver I
Resolver I

Any help please, i will give more information if needed. 

OvidiuNeacsu
Resolver I
Resolver I

PBIDesktop_ah1wvJsbNr.png

 

I don't know why it didn't take my last pic.

 

Thank you very much for all your help! You guys are amazing!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.