cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RichXM655
Regular Visitor

Categories or metadata for Columns.

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 .

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Categories or metadata for Columns.

@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:

1.PNG 

 

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

2.PNG  

 

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"))

3.PNG 

 

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.

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: Categories or metadata for Columns.

@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
Regular Visitor

Re: Categories or metadata for Columns.

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

ResponseIDDateHow 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 centreHow 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?
101/01/202042424432
202/01/202044433241
303/01/202014233143
404/01/202032324132
505/01/202033241344
606/01/202033433242
707/01/202024114434
808/01/202033141223
909/01/202033132444
1010/01/202014414322

 

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:

 

QuestionCategory ACategory B
How Satisfied are you with knowledge level of your mentor?MentorContent
How would you rate the availablity of your mentor?Mentor 
How satisfied are you with the accesibility of the training centre?CentreAccesibility
How satisfied arew you with the facilities of the training centreCentre 
How easily have you been able to access the course materials?ContentAccessibility
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 User IV
Super User IV

Re: Categories or metadata for Columns.

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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Community Support
Community Support

Re: Categories or metadata for Columns.

@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:

1.PNG 

 

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

2.PNG  

 

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"))

3.PNG 

 

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.

View solution in original post

RichXM655
Regular Visitor

Re: Categories or metadata for Columns.

This looks like a fantastic solution, thanks.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors