Calculate Percent of Survey Respondents By Group

[afaherty]

Greetings All,

I am extremely new to Power BI - so new that my employer just gave me access to it yesterday.  I need to take Likert scale type survey data and graph the responses.  Some of the survey takers are 1st year teachers, some are 3rd year teachers, and some are the employers.  The teachers rated themselves on various measures, and the employers rated them on the same measures.  They were given answer choices of consistently, inconsistently, minimally, and not at all.  I was thinking of creating stacked bar charts for each question.  Can someone assist me with how to have PowerBI calculate for me the percent of each answer by respondent type?  This is fake data below, but as you can see, some of the respondents didn't answer each question.

Thanks!

[Greg_Deckler]

Text data or sample PBIX is always appreciated. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I would unpivot your Question columns in Power Query and then the problem should be trivial. 

[afaherty]

@Greg_Deckler  Thanks!  Forgive me, I'm a newbie - can you give some step-by-step instructions for both the unpivot suggestion and the EARLIER DAX suggestion?

Group	Question1	Question2	Question3
1st Yr Teacher	Consistently	Inconsistently	Not at all
1st Yr Teacher	Consistently	Not at all	Minimally
1st Yr Teacher	Consistently	Inconsistently
1st Yr Teacher		Consistently	Consistently
1st Yr Teacher	Inconsistently	Minimally	Inconsistently
1st Yr Teacher	Inconsistently	Consistently	Consistently
1st Yr Teacher	Minimally	Not at all	Inconsistently
1st Yr Teacher	Minimally	Consistently
1st Yr Teacher		Minimally	Minimally
1st Yr Teacher	Not at all	Consistently	Inconsistently
3rd Yr Teacher	Minimally	Minimally	Not at all
3rd Yr Teacher		Inconsistently
3rd Yr Teacher	Consistently	Consistently	Inconsistently
3rd Yr Teacher	Inconsistently		Consistently
3rd Yr Teacher	Minimally	Not at all	Minimally
3rd Yr Teacher	Not at all	Consistently	Not at all
Employer	Consistently	Inconsistently	Inconsistently
Employer	Inconsistently		Minimally
Employer	Inconsistently	Minimally	Consistently
Employer	Inconsistently	Inconsistently	Minimally
Employer	Minimally	Consistently	Consistently

[Greg_Deckler]

No worries. Awesome, I attached a PBIX file. In Query Editor, use Shift to select all threee of your Question columns and then right-click and choose Unpivot. You can rename the columns if desired. After that it is dirt simple to create a stacked bar chart from the data, I have put one in the PBIX for you.

[afaherty]

@Greg_Deckler You're awesome! I hate to be so needy, but my PowerBI is yelling at me when I attempt to open your file - it says it's not compatible with my version of PBI, and I need to download the newest version.  However, I can't because my employer won't let me.  Is there anything I can do to get the file to open in PBI? Thank you SO much!

[Greg_Deckler]

Ugh, I wish Microsoft would put in a "compatibility mode" save feature. Easiest thing would be this, here is the Power Query code, you can paste this into Advanced Editor on a blank query to see how it works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZO9CsMgEMdfRZyzpHmEUEqHFgpdSsgQUqGC0aBmyNvXTj3jebGFDKfe7+5/H+k6frJmmXnFb4twXhpdA/sA7Ib3Vcdr59nDsrsYxpew4bk12knnhfZqDcezHuOLq/FsCJ9SRTxwr/hFajkFc/0vNcMxlqLREYWS4F9tyWNZgJ8lwIxRm0rSQ7gtbhOkdoYRKaIn8+Eb+8wLzFSKciw/+cSXanmZSiQTMjqyOGLDE45q6qYxx2lWZi36L5BCAY2WGOukvLOLtgcSkTYosctxxv4N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Question1", type text}, {"Question2", type text}, {"Question3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Group"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}})
in
    #"Renamed Columns"

Here is a screen shot of the visual and its configuration:

[afaherty]

I think there's a little miscommunication on my part.  So, I'm trying to get PBI to show the percentage of each of the 3 groups who answered the way they answered.  I am able to created a stacked bar graph for question 1 that is similar to what I need, but it's using the total number of responses altogether for question 1 as the denominator. I need it to use the number of responses by group as denominator. It's close but not quite what I need.  Below is what the graph is showing, and what I want it to show in black text:

[afaherty]

@Greg_Deckler I just realized my image didn't come through on my last reply.   Here it is:

[HotChilli]

You are nearly there. It's just that you changed the field in the Values well to show value as 'percent of grand total'.  You can see that the size of the bars is correct according to your desired result.  It's just the label you don't like (even though it's doing what you asked)

Just switch that value back to 'Show value as-> no calculation'.

Also I think if you keep clicking through the message about a different version of powerbi, it still opens correctly.