Forum Discussion
Solved
Get Survey Summary By Org
I need to get a summary of Survey Response by ORG. For example,
I need to get % summary of Survey Responses for ORG 1 --> Grouped By Category (Cat1, Cat 2, Cat3) -->drill down to individual Q's
1 & 0 are true or false responses to survey questions.
Does anyone know how this can be acheieved?
| Org Name | Department Name | Cat 1 Level 1 Q1 | Cat 1 Level 1 Q2 | Cat 1 Level 2 Q1 | Cat 1 Level 2 Q2 | Cat 1 Level 2 Q3 | Cat 2 Level 1 Q1 | Cat 2 Level 2 Q1 | Cat 2 Level 2 Q2 | Cat 2 Level 2 Q3 | Cat 2 Level 3 Q1 | Cat 2 Level 3 Q2 | Cat 3 Level 3 Q3 |
| Org 1 | Department 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| Org 1 | Department 2 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 1 |
| Org 1 | Department 3 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
| Org 2 | Department 4 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 1 |
| Org 2 | Department 5 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
| Org 3 | Department 6 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Org 3 | Department 7 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 1 |
| Org 3 | Department 8 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 |
| Org 3 | Department 9 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 0 | 1 |
| Org 4 | Department 10 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 0 |
| Org 4 | Department 11 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 |
| Org 4 | Department 12 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 |
| Org 5 | Department 13 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Org 5 | Department 14 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Org 5 | Department 15 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
Hi PowerBiNJ ,
Please update the applied codes in your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7DoMwDAXQX6kyM0AePPbufADK0AExgRDi/4VAqsDmXpqhg4co8okdu+tMuwyvwmTm3c+fZR37aT2Oe+QPIe9jBiFLEopLSJRADiRqUD7yhayEPEE4SKBA2kGwhJyESpLAg0BVQjX4sxVUw8nc2/1ZUUPaYh9/VuTVQuKtuyunxqSn4aMl4DVZOOWU5Q5KckBJWwEt+b9JgXbBOoxxAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Name" = _t, #"Department Name" = _t, #"Cat 1 Level 1 Q1" = _t, #"Cat 1 Level 1 Q2" = _t, #"Cat 1 Level 2 Q1" = _t, #"Cat 1 Level 2 Q2" = _t, #"Cat 1 Level 2 Q3" = _t, #"Cat 2 Level 1 Q1" = _t, #"Cat 2 Level 2 Q1" = _t, #"Cat 2 Level 2 Q2" = _t, #"Cat 2 Level 2 Q3" = _t, #"Cat 2 Level 3 Q1" = _t, #"Cat 2 Level 3 Q2" = _t, #"Cat 3 Level 3 Q3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org Name", type text}, {"Department Name", type text}, {"Cat 1 Level 1 Q1", Int64.Type}, {"Cat 1 Level 1 Q2", Int64.Type}, {"Cat 1 Level 2 Q1", Int64.Type}, {"Cat 1 Level 2 Q2", Int64.Type}, {"Cat 1 Level 2 Q3", Int64.Type}, {"Cat 2 Level 1 Q1", Int64.Type}, {"Cat 2 Level 2 Q1", Int64.Type}, {"Cat 2 Level 2 Q2", Int64.Type}, {"Cat 2 Level 2 Q3", Int64.Type}, {"Cat 2 Level 3 Q1", Int64.Type}, {"Cat 2 Level 3 Q2", Int64.Type}, {"Cat 3 Level 3 Q3", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Cat 1 Level 1 Q1", "Cat 1 Level 1 Q2", "Cat 1 Level 2 Q1", "Cat 1 Level 2 Q2", "Cat 1 Level 2 Q3", "Cat 2 Level 1 Q1", "Cat 2 Level 2 Q1", "Cat 2 Level 2 Q2", "Cat 2 Level 2 Q3", "Cat 2 Level 3 Q1", "Cat 2 Level 3 Q2", "Cat 3 Level 3 Q3"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Cat ","Cat", Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Level ","Level",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Category", "Level", "Question"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Level", type text}, {"Question", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Question]), "Question", "Value", List.Sum) in #"Pivoted Column"Best Regards
4 Replies
- amitchandak
Super User
PowerBiNJ , Try if the solution for double header can help
How to Deal with Double Headers in Power BI: https://youtu.be/1e16oHv9kTk
Hi PowerBiNJ ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regards
Hi, thank you for working on this...Is it possible to not merge all Q1,Q2,Q3 together? Every Question is different in in Category and Level.
Can i just remove the last part "Question" of the Transform Colummn Type-->Split By Column?
Hi PowerBiNJ ,
Please update the applied codes in your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7DoMwDAXQX6kyM0AePPbufADK0AExgRDi/4VAqsDmXpqhg4co8okdu+tMuwyvwmTm3c+fZR37aT2Oe+QPIe9jBiFLEopLSJRADiRqUD7yhayEPEE4SKBA2kGwhJyESpLAg0BVQjX4sxVUw8nc2/1ZUUPaYh9/VuTVQuKtuyunxqSn4aMl4DVZOOWU5Q5KckBJWwEt+b9JgXbBOoxxAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Name" = _t, #"Department Name" = _t, #"Cat 1 Level 1 Q1" = _t, #"Cat 1 Level 1 Q2" = _t, #"Cat 1 Level 2 Q1" = _t, #"Cat 1 Level 2 Q2" = _t, #"Cat 1 Level 2 Q3" = _t, #"Cat 2 Level 1 Q1" = _t, #"Cat 2 Level 2 Q1" = _t, #"Cat 2 Level 2 Q2" = _t, #"Cat 2 Level 2 Q3" = _t, #"Cat 2 Level 3 Q1" = _t, #"Cat 2 Level 3 Q2" = _t, #"Cat 3 Level 3 Q3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org Name", type text}, {"Department Name", type text}, {"Cat 1 Level 1 Q1", Int64.Type}, {"Cat 1 Level 1 Q2", Int64.Type}, {"Cat 1 Level 2 Q1", Int64.Type}, {"Cat 1 Level 2 Q2", Int64.Type}, {"Cat 1 Level 2 Q3", Int64.Type}, {"Cat 2 Level 1 Q1", Int64.Type}, {"Cat 2 Level 2 Q1", Int64.Type}, {"Cat 2 Level 2 Q2", Int64.Type}, {"Cat 2 Level 2 Q3", Int64.Type}, {"Cat 2 Level 3 Q1", Int64.Type}, {"Cat 2 Level 3 Q2", Int64.Type}, {"Cat 3 Level 3 Q3", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Cat 1 Level 1 Q1", "Cat 1 Level 1 Q2", "Cat 1 Level 2 Q1", "Cat 1 Level 2 Q2", "Cat 1 Level 2 Q3", "Cat 2 Level 1 Q1", "Cat 2 Level 2 Q1", "Cat 2 Level 2 Q2", "Cat 2 Level 2 Q3", "Cat 2 Level 3 Q1", "Cat 2 Level 3 Q2", "Cat 3 Level 3 Q3"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Cat ","Cat", Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Level ","Level",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Category", "Level", "Question"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Level", type text}, {"Question", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Question]), "Question", "Value", List.Sum) in #"Pivoted Column"Best Regards
