Google Form Graph

[IanErler]

Hello,

I started with learning how to use Power BI recently. And I'm having trouble reconstruction a certain graph.

I've used google forms for my survey and exported the data to a .xls file. There after I used this file to fill my Power BI application.

Now I'm trying to recreate the following diagram for analytics:

The diagram was made up of the following question:

However because of this the structure of the data is stored as follows:

Ands the graph appear in Power BI like that:

How can I recreate the graph?

[Anonymous · Accepted solution]

Hi @IanErler,

It seems like you are trying to expand all text string and get the detail count of each item, right? If this is the case, you can take a look at the following link about the similar requirements:
Occurrence of value (or) count value(text) in a column 

NEW Table =
VAR _path =
    SUBSTITUTE ( CONCATENATEX ( ALL ( 'Table'[Text] ), [Text], "," ), ",", "|" )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Text", PATHITEM ( _path, [Value] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _pathtable, [Text] ),
        "Count", COUNTROWS ( FILTER ( _pathtable, [Text] = EARLIER ( [Text] ) ) )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous 

Thanks for posting this info.  I used the information provided to create a new table.  However is is not counting each unique occurance of each response, any ideas.  Below are screen shots to elaborate.

Here is a sample of my data

When I filter to the specific response of "I need more info..."  I see I have 32 rows with this response, some with additional responses.

But when I use the above Table measure, I get 11 responses for "I need more info...."

What can I do to get my table to count each unique instance.

[IanErler]

Hello @Anonymous,

Thanks a lot for your help. I tried to understand what you did here, but failed to understand the basic.

But the code worked. I also needed the % of people that answered each option, so I added a new columm dividing the count columm value by the number of people that answered the survey (149), as you can be seen in the pictures below. Do you know if I can show the two values in the graph (the numeric and the %), like the Google one, using the Power BI?

Table created!

 With a percentage columm now

My Graph

[mahoneypat]

Since it is multi-select response and you are getting a comma-separated list of the responses, you can use the following steps to split them out into new rows:

• Rename the responses column to "Responses" (not required, just easier than the long title of your question)
• Add a custom column with this formula: = Text.Split([Responses], ", ")
• Click on the expand in the header for the new List column you created, and choose expand to new rows
• Write a simple measure to count the values in that column and make your visual

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[IanErler]

Hello @mahoneypat,

Thanks for you help! But I am a noob here. There's a measure that count all the values in a columm? I found a funcion for count individual values, like the one below, and created the 8 measures using it.

The Response columm wth the new one and the measures

But when I tried to make the visual, it was weird. There was too much space between the bars (that I couldn't reduce), the name of the values were unaligned with the bars, I couldn't define the order. Something went wrong.

[rubinboer]

hi there i recreated your scenario and cannot find the visual to react the way yours are doing. is it possible to share the power bi *.pbx file?

the column1 represent the response and the count is just the same column and the count function