Forum Discussion
Need help with Stacked bar chart
Hi there,
I am new to PowerBI so please bear with me.
I have this table:
| RespondentID | Question 1 | Question 2 | Question 3 |
| 1 | Neither Agree or Disagree | Agree | Agree |
| 2 | Disagree | Agree | Agree |
| 3 | Neither Agree or Disagree | Neither Agree or Disagree | Neither Agree or Disagree |
| 4 | Agree | Disagree | Neither Agree or Disagree |
and I want to view it as a stacked bar chart, showing the question number to the left and for each question, a bar coloured depending on the count of values (disagree, agree, neither)
Thanks,
Hi zd ,
Please check the following steps as below.
1. Transpose your data in power query as below. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzSzJSC1ScEwvSk1VyC9ScMksTgSxgXKOKHSsTrSSEZCHV4ExASPJkwOZbIJkH3GaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RespondentID = _t, #"Question 1" = _t, #"Question 2" = _t, #"Question 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"RespondentID", Int64.Type}, {"Question 1", type text}, {"Question 2", type text}, {"Question 3", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"RespondentID", type text}, {"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"RespondentID", "question no"}}) in #"Renamed Columns"2. Create a calculated table and create relationship with fact table.
Table = VALUES(Table1[4])
3. After that, we can create a measure to get our excepted result.
Measure 2 = COUNTROWS ( Table1 ) + CALCULATE ( COUNTROWS ( 'Table1' ), USERELATIONSHIP ( Table1[1], 'Table'[4] ) ) + CALCULATE ( COUNTROWS ( Table1 ), USERELATIONSHIP ( Table1[2], 'Table'[4] ) ) + CALCULATE ( COUNTROWS ( Table1 ), USERELATIONSHIP ( Table1[3], 'Table'[4] ) )
4 Replies
Hi zd ,
Please check the following steps as below.
1. Transpose your data in power query as below. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzSzJSC1ScEwvSk1VyC9ScMksTgSxgXKOKHSsTrSSEZCHV4ExASPJkwOZbIJkH3GaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RespondentID = _t, #"Question 1" = _t, #"Question 2" = _t, #"Question 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"RespondentID", Int64.Type}, {"Question 1", type text}, {"Question 2", type text}, {"Question 3", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"RespondentID", type text}, {"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"RespondentID", "question no"}}) in #"Renamed Columns"2. Create a calculated table and create relationship with fact table.
Table = VALUES(Table1[4])
3. After that, we can create a measure to get our excepted result.
Measure 2 = COUNTROWS ( Table1 ) + CALCULATE ( COUNTROWS ( 'Table1' ), USERELATIONSHIP ( Table1[1], 'Table'[4] ) ) + CALCULATE ( COUNTROWS ( Table1 ), USERELATIONSHIP ( Table1[2], 'Table'[4] ) ) + CALCULATE ( COUNTROWS ( Table1 ), USERELATIONSHIP ( Table1[3], 'Table'[4] ) )Hi v-frfei-msft
So, I transposed and then used the 1st row as headers.
Then I created the calculated table and created relationships with the table we have, with the column containing all the possible answers.
Then I created the measure.
But this is what I got:A row for each possible answer. Where did I go wrong?
And can you please add brief explanations to each step?
Sorry am just new to this so not entirely sure what each step actually does.
Appreciate your help!
ThanksThanks v-frfei-msft I have managed to get it to work, it's perfect!
One last question, since I have many respondents for each question, do I have to create a relationship for each respondent (column)? Is there a way to automate this process?
