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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aimeeclaird
Helper IV
Helper IV

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

 

View solution in original post

10 REPLIES 10
littlemojopuppy
Community Champion
Community Champion

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
Super User

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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