Forum Discussion
Helper ICategories or metadata for Columns.
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.
Helper ISo, 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".
Super UserI 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.