One question with multiple ratings (weigh) in one visual

[pehuang1003]

Hi all, 

I am new to PowerBi.  I've digged in many tutorial videos but still couldn't find the solution, need help!

I am trying to represent a survey feedback as a visual:

 - It is one question with 10 categories 

 - Each category receives a rating (scale 1 - 5)

 - Category is x-asis and how many people rate the category is y-axis

This question is part of a big survey and it's imported in PowerBI as sheet with all other data.

Here's the data sheet view in powerbi:

and here's the visual:

But this is not what I want.  I need to show visual as this one:

I generated the correct one using a separate sheet which counts how many people rate each category based on the scale, which is the thing that is missing in the big data sheet got exported into powerbi because the survy tool is not able to include it in the big sheet.  I'd like to know what I need to do to create the correct visual.  

Would it be adding calculations into the big sheet or I will have to create relationship between two sheets?

Thank you in advance for helping me out on this.

[v-piga-msft]

Hi @pehuang1003,

Have you solved your problem, if you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[MFelix]

Hi @pehuang1003,

On the query editor you should unpivot the columns with you answer so you would get 2 columns:

- Values (3 - meets my needs, ....)

- Attribute (Q6_reliability, ...)

Then you can set up the graph as you need.

Regards,

MFelix

[pehuang1003]

Hi MFelix,

Encounter problems when I unpivolt another set of columns in the same sheet.

The visual generate the combination of the values, I think.

It looks like this now:

The count shows on the data lable is way off...

It should be like this:

I unpivot Q8 quesitons (below) which causes the problem.

Is there a way to separate the Value for these two quesitons?

Thank you.

Penny 

[pehuang1003]

I think I solve this problem, not sure if this is the smart/right way to do it but I was able to separate the value count.

I duplcicate the sheet twice, one is for Q6 and the other one is for Q8, then I unpivot only Q6 quesitos in the first duplicate sheet and unpivot the Q8 questions in the second duplicate sheet.

In the visual, I select data from the two separate sheet so the count of value is not combined.

Please let me know if there's a better way.

Thank you.

Penny 

[MFelix]

Hi @pehuang1003,

I would recomend that you unpivot all your questions no matter what they are then create on table with distinct value for the questions that you will relate with your source table, then create measures accordingly to your need in this case if you only need the Q6 questions  you should do something like this:

Q6 =
CALCULATE (
    COUNT ( Questions[Value] ),
    LEFT ( Questions[Attribute], 2 ) = "Q6"
)

In this case you would use the DimTable for questions as your axis and your measures in the table this will filter out the blanks value of the questions:

See attach PBIX file.

Regards,

MFelix

[pehuang1003]

Hi MFelix,

Thanks! I unpivot all as you instructed but I don't know how to get the response count.

This is my unpivot data:

How to get the response count as you provided in your example?

[MFelix]

You should calculate the measures similar to what.I posted in the previous reply.

Regsrds,
MFelix

[phuang]

I thought I've replied.  It works!  Thank you very much for your help 🙂 

[pehuang1003]

MFelix,

Thank you, it worked! 🙂

[pehuang1003]

MFelix,

I have a cloumn that contains feedbacks for Q6.

Questions are as below on my survey:

Q6. Please rate the attributes... (contains 10 questions)

Q7. Please porvide specifi examples to support your feedback (rating) in Q6 - this is just a text box

I want to be able to see customer's feedback when my mouse clicks on the chart, such as who left the feedback and content, should I use drill?

I didn't unpivot Q7 column when I unpivot Q6, would I need to unpivot Q7 in order to use the drill?

Thank you.