Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to Solution.
@afaherty put try in the beginning
@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"
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.
@afaherty pbix is attached
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?
@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"
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).
@afaherty put try in the beginning
THANK YOU!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |