Categories or metadata for Columns.

[RichXM655]

Hi.

I've got an upcoming project where I will be feeding the results of a large survey (about 500 questions, 30k responses) into powerBI to make a report.

The client has defined some of the questions as being grouped by overall topics, though these are not contiguous within the way the survey is designed. 

As simplified example, imagine I;ve got a simple survey about following up on an online purchase - all answering on a 1-10 scale

1: how easty was it to find the product on oyr website

2: how visually appealing did you find the website

3: how satsified are you with the product?

4: how likely are you to buy that product again, or recommend to a friend?

SO Question 1 and 2 are relating to the "website" category , and 3 and 4 to the "product" category.

The data would be returned from the survey software in the format where each row would be a response to the survey, and each question is a column.

The easiest solution to providing collated "category" level data would be to create measures that calculate an overall average for the columns, but that's going to get very laborious to create these manually for the real survey.

Seeing as I already have a lookup table for the questions and the categories they bleong to, is there some way of joining the two up so that these work as (basically) metadata, or perhaps filter pre-sets .

[v-yuta-msft]

@RichXM655 ,

I'm not clear about your requirement. Could you please share some sample data and clarify more details about your requirement?

Regards,

Jimmy Tao

[RichXM655]

So, imagine I've got this export from a survey (I;ve changed the example survey, as it's not important)

ResponseID	Date	How Satisfied are you with knowledge level of your mentor?	How would you rate the availablity of your mentor?	How satisfied are you with the accesibility of the training centre?	How satisfied arew you with the facilities of the training centre	How satisfied arew you with the content of the course?	How easily have you been able to access the course materials?	How useful has the training prgramme been to you in the time since starting?	Would you recommend this programme to others?
1	01/01/2020	4	2	4	2	4	4	3	2
2	02/01/2020	4	4	4	3	3	2	4	1
3	03/01/2020	1	4	2	3	3	1	4	3
4	04/01/2020	3	2	3	2	4	1	3	2
5	05/01/2020	3	3	2	4	1	3	4	4
6	06/01/2020	3	3	4	3	3	2	4	2
7	07/01/2020	2	4	1	1	4	4	3	4
8	08/01/2020	3	3	1	4	1	2	2	3
9	09/01/2020	3	3	1	3	2	4	4	4
10	10/01/2020	1	4	4	1	4	3	2	2

The client, in addition to wanting to see the usual reporting for each individual question, would also like to see combined results for particular categories (or topics). They've provided a list of which questions relate to which topics in this format:

Question	Category A	Category B
How Satisfied are you with knowledge level of your mentor?	Mentor	Content
How would you rate the availablity of your mentor?	Mentor
How satisfied are you with the accesibility of the training centre?	Centre	Accesibility
How satisfied arew you with the facilities of the training centre	Centre
How easily have you been able to access the course materials?	Content	Accessibility
How satisfied arew you with the content of the course?	Content
How useful has the training prgramme been to you in the time since starting?	Overall
Would you recommend this programme to others?	Overall

So - I know I could manually create measures (or indeed, additional columns in the response data) that would create combined / averaged values for these categories. But doing this for the real survey will be extremely laborious, as there's a lot more than 5 categories.

What I'm trying to find out is if there's some way of doing this so that I can create measures where the code is basically "Just use the "Mentor" values for this calculation.", or "show me values sorted by category".

[v-yuta-msft · Accepted solution]

@RichXM655 ,

In query editor, click on all the questions() and then click transform-> Unpivot Columns, you will get two new columns [Attribute] and [Value] as below:

 

After applied&close, you need to build a one-many relationship between these two tables as below:

  

Then you can create a measure to calculate the average value of Mentor in category A like pattern below:

Average_Value_Of_Mentor = CALCULATE(AVERAGE(Table1[Value]), FILTER(Table2, Table2[Category A] = "Mentor"))

 

You can also refer to the pbix attached.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RichXM655]

This looks like a fantastic solution, thanks.

[amitchandak]

I think first thing you should do is unpivot the first table 

https://radacad.com/pivot-and-unpivot-with-power-bi

Then it join with second table on questions. As the second table is like master to it. 

In case you again need to pivot your calculation you can use matrix.