Need help with Stacked bar chart

[zd]

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,

[v-frfei-msft · Accepted solution]

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] ) )

[v-frfei-msft · Accepted solution]

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] ) )

[zd]

Thanks @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? 

[v-frfei-msft · Accepted solution]

Hi @zd ,

Step1 DemoteHeaders:

Step2 : Transpose table

Step3:PromoteHeaders

[zd]

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!
Thanks