Survey Data - Percentages based on categories(?), Columns

[Aimeeclaird]

Hi, I am working with some survey data that I would like to present as % Copy of example data can be found here: https://data2actionltd.sharepoint.com/:x:/g/EVVfO7RCBtRIoAE2ojljtHYBZObVm4C5b2pxFQjd29GjPA I am trying to answer: - For Q3a, what percentage of the base responded by each type. E.g. of base 2, 30% responded always, 20% responded etc. I'd like to use a visual where the base on the the X axis, and i'd like to include the overall % too - so we can compare how a school responses vs the entire data set/project.

[StefanoGrimaldi · Accepted solution]

HEY,

first you need to tranform that data on power query: 

select the base columns (not the one with the questions) and use the unpivot others column function here, so any new question added to the data get in the tranformation correctly. 

them you can add this to a simple matrix first option: 

and them on the value field format you put it on row total value % and will give the value as you looking for. 

[Aimeeclaird]

Hi @StefanoGrimaldi 

Thanks for the reply. I've tried this solution myself however it's not providing the outcome I need. 

Perhaps I wasn't clear enough, and i've also had the thought of visualising responses by question (rather than all questions together). Would it be possible to have something like this:

Q3a = 

Col 1 = Base

Col 2 = Always

Col 3 = Often

Col 4 = Sometimes

Col 5 = Rarely

Col 6 = Never

And then based on the number of responses received per base, calculate the percentage of responses against each type (always, sometimes etc.)? 

It's critical that my percentages are not based on the entire dataset as there are varying numbers of responses per base. 

Hope this clarifies my requirements!

Thanks