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.
Hi,
We have a survey, with a set number of questions, each question is assigned a "camp" they belong to.
A person can be considered engaged in a camp if the average of their answers for the questions (for that camp) is greater than or equal to 3.
I am needing to get a measure that will tell me the number of engaged employees for each camp.
My approach would be to create a column, which is the average of answer, grouped by camp and ID, return IF AVG(ianswer) is >= 3," Engaged", else "Disengaged". However i am struggling with the correct syntax.
Currently, my data set looks like this,
UserID | QuestionID | Camp | Answer |
1 | 1 | 1 | 3 |
1 | 2 | 1 | 4 |
1 | 3 | 2 | 2 |
1 | 4 | 2 | 4 |
2 | 1 | 1 | 4 |
2 | 2 | 1 | 4 |
2 | 3 | 2 | 2 |
2 | 4 | 2 | 1 |
My desired output is
UserID | QuestionID | Camp | Answer | Camp Engaged |
1 | 1 | 1 | 3 | Engaged |
1 | 2 | 1 | 4 | Engaged |
1 | 3 | 2 | 2 | Engaged |
1 | 4 | 2 | 4 | Engaged |
2 | 1 | 1 | 4 | Engaged |
2 | 2 | 1 | 4 | Engaged |
2 | 3 | 2 | 3 | Disengaged |
2 | 4 | 2 | 1 | Disengaged |
Would appreciate any tips for this!
Solved! Go to Solution.
Try
Camp Engaged = if(AVERAGEX(FILTER('Table','Table'[UserID]=EARLIER('Table'[UserID]) && 'Table'[Camp]=EARLIER('Table'[Camp])),'Table'[Answer])>=3,"Engaged","Disengaged")
refer
https://www.dropbox.com/s/lr09ews92zhlka5/898625.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Try
Camp Engaged = if(AVERAGEX(FILTER('Table','Table'[UserID]=EARLIER('Table'[UserID]) && 'Table'[Camp]=EARLIER('Table'[Camp])),'Table'[Answer])>=3,"Engaged","Disengaged")
refer
https://www.dropbox.com/s/lr09ews92zhlka5/898625.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Perfect, appreciated the PIBX file too.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |