cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aimeeclaird
Helper III
Helper III

Chart showing all data and line based on selected category (Column and line visual)

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:

SchoolQ1Q2Q3
School A544
School A333
School A235
School A554
School B433
School B435
School B544
School B345
School B455

 

1 ACCEPTED 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))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

10 REPLIES 10
littlemojopuppy
Super User I
Super User I

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])
	)
)

Hi @littlemojopuppy 

 

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,

@Aimeeclaird very basic...but something like this?

littlemojopuppy_0-1610034803333.png

 

@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. 

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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!PBI SS 07012021.JPG

@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))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @amitchandak This is such a good work around. Just set up and it's working brilliantly! Thanks for the help 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors