Need help with counting the amounts yes, no and partly

[Brndn]

Hi,

I'm facing some problems when trying to count the amounts yes, no and partly for all the questions in one. 

My goal is to visualize the amounts of yes, no and partly through a bar/pie chart en be able to filter it with the first three columns. 

So in the example below, the total amount of yes is 12, no 14 and partly 5 should be showed through the bar/pie chart.

Country	Region	Type	Question 1	Question 2	Question 3	Question 4	Question 5	Question 6
The Netherlands	North	Supermarket	Yes	Yes				No
The Netherlands	West	Cinema	No	Yes	Partly		Yes	No
The Netherlands	East	Cinema		Yes		No	No	Partly
The Netherlands	South	Supermarket	No			No
Belgium	North	Supermarket			partly	No	No	No
Belgium	West	Cinema	Yes	No		Yes	Yes	Yes
Belgium	East	Cinema		No	No	Yes	Partly	Yes
Belgium	South	Supermarket	Partly		Yes			No

Thanks in advance!!

[Anonymous · Accepted solution]

Hi @Brndn ,

Select 6 question columns and click unpivot feature in Query Editor and you will get a new table.

Then create visuals as below.

Best Regards,

Jay

[Brndn]

Thank you so much! Exactly what I was looking for

[DataInsights]

@Brndn,

Try this solution.

1. Unpivot the Question columns in Power Query:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "dZHPCsMgDMZfRTz3KTZ2lUEHY5QehIVZprX457C3b6u2apcejET8fsmXdB19CCAMnAAj+fi2tKFMGyeWu/UTGMXNF9ySvcDukRSHado3GOYJdtVdhxEUjx83/Z0bJ38JEJ9OMTdeY0jVRYCGkJg4pNUesRR0pCTFZGVcQH4Gr07HscmmzUruJFnJhOMkdselmxxrMeY/1zrM81+OO0cWUO6znwE=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Country = _t, 
        Region = _t, 
        Type = _t, 
        #"Question 1" = _t, 
        #"Question 2" = _t, 
        #"Question 3" = _t, 
        #"Question 4" = _t, 
        #"Question 5" = _t, 
        #"Question 6" = _t
      ]
  ), 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {
      {"Country", type text}, 
      {"Region", type text}, 
      {"Type", type text}, 
      {"Question 1", type text}, 
      {"Question 2", type text}, 
      {"Question 3", type text}, 
      {"Question 4", type text}, 
      {"Question 5", type text}, 
      {"Question 6", type text}
    }
  ), 
  UnpivotColumns = Table.UnpivotOtherColumns(
    ChangeType, 
    {"Country", "Region", "Type"}, 
    "Attribute", 
    "Value"
  ), 
  RenameColumns = Table.RenameColumns(UnpivotColumns, {{"Attribute", "Question"}}), 
  FilterBlanks = Table.SelectRows(RenameColumns, each ([Value] <> " "))
in
  FilterBlanks

2. Create measure:

Count Value = COUNT ( Questions[Value] )