Survey data - converting multiple columns in one table to categories, averages etc to chart

[Anonymous]

Hello

I am working with survey data and have asked the same three questions for a number of products. The exported survey results have a single column for each result which is making it difficult to chart or link tables together. I have been watching videos on calculating tables and linking tables but not getting far. 

When the information comes into POwer BI there is a single table with 9 columns with labels such as Product 1: Benefit, Product 1: Use, Product 1: Frequency, Product 2 Benefit etc. There is a numerical response which I have been able to make a List Measure of but don't know what to do next. 

The end result is ideally a Spider chart with Benefit, Use Freq as the axis and the average responses over the top.

I feel like I am missing a simple step to get some tables, categories, and calculations all happening. 

Any assistance greatly appreciated.

N

Desired end result

[Ashish_Mathur · Accepted solution]

Hi,

The "Unpivot other columns" feature should work.  To get specific help, share a dataset in a format that can be pasted in MS Excel.

[Anonymous]

Thanks so much! Worked perfectly. 

I then split columns further to get the product category and question. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

Sorry to ask a post-solution question. I have just realised my other charts are now showing different count data due to the number of extra rows that have been created using the Unpivot process. Should I split the table and create a relationship somehow so the other data isnt impacted?

Nic

[Ashish_Mathur]

Hi,

See if the DISTINCTCOUNT() function solves the problem.  If not, then share the link from where i can download your PBI file and show the expected result.

[Anonymous]

Thanks again for the speedy reply. 

Yes, Distinct worked, but only worked in a couple of instances. I ended up going back before the Unpivot and duplicated the table, created a link and then redid the Unpivot so it only effected the duplicated/cut down table. Worked a treat.  

[Ashish_Mathur]

You are welcome.