Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone, hoping for some help creating a stacked bar chart with the data I have. If a stacked bar chart is not the best option, I'm definitely open to any other ideas for how to present this.
The goal is to show how many members total ranked each location, broken down by many ranked it 1st, 2nd, 3rd, etc. I hope this makes sense.
I have 10 possible location choices, and each individual ranks their choices. My assumption was to create a horizontal bar chart where the locations are listed on the Y axis and the legend color codes each preference category (pref 1, pref 2, pref 3, etc.). So the goal is to have a bar for Oklahoma and within that bar, how many ranked it 1st, 2nd, 3rd, etc. I hope this makes sense.
An example of how the data is formatted is below. The member numbers are unique and are formatted as text.
Member Number | Preference 1 | Preference 2 | Preference 3 |
1234 | Oklahoma | New York | Florida |
5678 | New York | Florida | South Carolina |
9101 | Florida | Oklahoma | South Carolina |
1112 | South Carolina | Florida | n/a |
Solved! Go to Solution.
First step - unpivot your data to make it usable.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfLPzknMyM9NBDL9UssVIvOLsoFMt5z8osyURKVYnWglUzNzC+yyOkrB+aUlGQrOiUX5OZl5EOWWhgaGKGqQbMCi3NDQ0AhTBll/nj5QZSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member Number" = _t, #"Preference 1" = _t, #"Preference 2" = _t, #"Preference 3" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Member Number"}, "Preference", "Location"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Preference ","",Replacer.ReplaceText,{"Preference"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Preference", Int64.Type}})
in
#"Changed Type"
See if a matrix visual gives you better insights.
see attached
First step - unpivot your data to make it usable.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfLPzknMyM9NBDL9UssVIvOLsoFMt5z8osyURKVYnWglUzNzC+yyOkrB+aUlGQrOiUX5OZl5EOWWhgaGKGqQbMCi3NDQ0AhTBll/nj5QZSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member Number" = _t, #"Preference 1" = _t, #"Preference 2" = _t, #"Preference 3" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Member Number"}, "Preference", "Location"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Preference ","",Replacer.ReplaceText,{"Preference"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Preference", Int64.Type}})
in
#"Changed Type"
See if a matrix visual gives you better insights.
see attached
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |