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, hoping someone can help please!
I have some survey data taken from Schools (apologies, therefore cannot attach my data). I have converted the text survey responses to numbers, and would like to show averages by question in a bar chart (or similar).
I'd then like to be able to compare a specific schools average responses as a line on the same visual, ideally with the ability to filter (perhaps using a slicer?) for individual schools.
I've tried to mock up an example of my data below if this helps:
School | Q1 | Q2 | Q3 |
School A | 5 | 4 | 4 |
School A | 3 | 3 | 3 |
School A | 2 | 3 | 5 |
School A | 5 | 5 | 4 |
School B | 4 | 3 | 3 |
School B | 4 | 3 | 5 |
School B | 5 | 4 | 4 |
School B | 3 | 4 | 5 |
School B | 4 | 5 | 5 |
Solved! Go to Solution.
@Aimeeclaird , so what I got you want one by selection and one without selection .
If you need bar not have filter and line to have filter.
The create an independent table for school, and do nor join with you table
Measure = calculate(Average(Table[Value]), filter(Table, Table[School] in values(School[school])))
Where school is new table
But if you measure for allselected try
Measure = calculate(Average(Table[Value]),allselected(Table))
Hi @Aimeeclaird I'd suggest using Power Query to unpivot your questions into a single field with the values Q1, Q2, etc. populating it. From there it should be fairly easy to calculate average by question with a formula like this
CALCULATE(
AVERAGE(Table[Value]),
FILTER(
Table,
Table[QuestionNumber] = SELECTEDVALUE(Table[QuestionNumber])
)
)
Thank you for your response! I've made it that far, and have a basic visual showing each question average for the entire data set. What i'd like to do (if it's possible) is to show each school (when one is selected/filtered) over the top of the entire data set.
E.g. it would be interesting to see that overall Q1 averaged a score of 3, however a specific school averaged a score of 5 for that question. Hope this clarifies my requirements!
Thanks,
@littlemojopuppy This displays the information I need to perfectly - can't believe I didn't think of this myself.
Although would be nice if there was a more 'snazzy' way of visualising the same?
@Aimeeclaird we can do snazzy 😉 Just wanted to make sure this is the kind of thing you're looking for
Thanks so much for replying @littlemojopuppy - appreciate the support but another user has provided a solution that is working.
@Aimeeclaird , not very clear. One or the way to take school as legend , Then what will be on the axis.
May be unpivot the data - https://www.youtube.com/watch?v=2HjkBtxSM0g
https://radacad.com/pivot-and-unpivot-with-power-bi
And the use question on axis and School on legend
Thanks for the reply @amitchandak
Apologies - I can see it in my head, struggling to articulate.
I've unpivoted, and can present the average scores for the entire data set (screenshot attached as not sensitive) however I'd now line to include a line to show a specific schools averages on top - that way I can see that the entire dataset averages a question at X and the schools average response for the same question was Y...
Hope this helps to clarify my requirements!
@Aimeeclaird , so what I got you want one by selection and one without selection .
If you need bar not have filter and line to have filter.
The create an independent table for school, and do nor join with you table
Measure = calculate(Average(Table[Value]), filter(Table, Table[School] in values(School[school])))
Where school is new table
But if you measure for allselected try
Measure = calculate(Average(Table[Value]),allselected(Table))
Hi @amitchandak This is such a good work around. Just set up and it's working brilliantly! Thanks for the help 🙂
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |