Text fields with more than 1 survey response in a cell - calculating percentages

[afaherty]

Hi all,

Here is some fake data in a similar format to what I am working with (though I'm not working with favorite colors!).

Grade	Teacher	Favorite Color(s)
8th	Smith	Blue
8th	Smith	Blue
8th	Rogers	Blue, Pink, Purple
8th	Lee	Purple
7th	Rogers	Green
7th	Rogers	Green, Black
7th	Lee	Blue
7th	Lee	Pink
7th	Smith	Green
6th	Rogers	Black, Green, Purple
6th	Lee	Black
6th	Smith	Purple, Black
6th	Rogers	Pink, Blue

If I wanted to produce a matrix of these results, along with a bar graph of the percentages and a slicer for teacher, how might I go about doing this considering the fact that some responses have more than one favorite color?  

	Blue		Pink		Purple		Green		Black
	N	%	N	%	N	%	N	%	N	%
6th	1	25%	1	25%	2	50%	1	25%	3	75%
7th	1	20%	1	20%	0	0%	3	60%	1	20%
8th	3	75%	1	25%	2	50%	0	0%	0	0%

I have been creating a new measure for each color to calculate the number of each favorite color this way:

Blue Count = CALCULATE(COUNTROWS('Colors'),'Colors',SEARCH("Blue",'Colors'[Favorite Colors],,0))+0

I also have a total measure adding them all up:

Total Colors = SUMX('Colors','Colors'[Blue Count]+'Colors'[Pink Count]+'Colors'[Purple Count]+'Colors'[Green Count]+'Colors'[Black Count])+0

...But then when I attempted to figure out the percentages for the bar graph, I got stuck.  

Anyone have any ideas?

Thank you!

[smpa01]

@afaherty  can you transform the table like this prior to analyzing

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsijJUNJRUgjOzYQwnHJKU5VidQhLBOWnpxYVw2R0FAIy87KBZGlRQQ6KOp/UVBCFJGGOboB7UWpqHh4pHaAVicnZyCqgpsIdhSoMcguyMNwTCJvMMH0BtEIHZiGSc83QbIS5xAzNbIgWHUwVCDsgYQRxdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, #" Teacher " = _t, #" Favorite Color(s)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Grade", type text}, {" Teacher ", type text}, {" Favorite Color(s)", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([#" Favorite Color(s)"],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{" Favorite Color(s)"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Custom", "Favorite Color(s)"}})
in
    #"Renamed Columns"

[afaherty]

Thank you for taking the time to write that up.  If I was as smart as you, I probably could do that!  But I honestly wouldn't even know where to start with that.  I'm still a bit new to the PowerBI/DAX world.

[smpa01]

@afaherty  pbix is attached

[afaherty]

Thank you!  I did exactly what you did, and I got errors because of the fact that some people left theirs blank:

What is the ideal way to handle this?  When I go to expand, it isn't able to properly carry out that step due to this issue.  I want to keep the blank ones though - don't want to filter them out.  Should I replace values and make all blanks "none" or something of that nature?

[smpa01]

@afaherty  change to this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsijJUNJRUgjOzYQwnHJKU5VidQhLBOWnpxYVw2R0FAIy87KBZGlRQQ6KOp/UVBCFJGGOboB7UWpqHh4pHaAVicnZyCqgpsIdhSoMcguyMNwTCJvMMH0BtEIHZiGSc83QbIS5xAzNbIgWHUwVCDsgYQR3NEQeZoBSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, #" Teacher " = _t, #" Favorite Color(s)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Grade", type text}, {" Teacher ", type text}, {" Favorite Color(s)", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Text.Split([#" Favorite Color(s)"],",") otherwise {"-999"}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{" Favorite Color(s)"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Custom", "Favorite Color(s)"}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Renamed Columns",{{" Teacher ", Text.Trim, type text}})
in
    #"Trimmed Text1"

[afaherty]

Thank you!  I am doing this in the query - what is the proper way to enter the "otherwise" part?  (The variables/columns etc in the screenshot are from my actual data here, not the favorite color example).

[smpa01 · Accepted solution]

@afaherty  put try in the beginning

[afaherty]

THANK YOU!!