Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EMC2
Frequent Visitor

Struggling to Create Stacked Bar Chart Showing Location Preferences

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 NumberPreference 1Preference 2Preference 3
1234OklahomaNew YorkFlorida
5678New YorkFloridaSouth Carolina
9101FloridaOklahomaSouth Carolina
1112South CarolinaFloridan/a

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

lbendlin_0-1665266395576.png

see attached

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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.

lbendlin_0-1665266395576.png

see attached

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.