Someone requested the files for this video - here's the github link (this is the version in the intro, I happened not to save the exact one in the walkthrough - they're very close though): github.com/chpayton/BI-Files/tree/main/Survey%20Data
Nice one. Bit of a few codes with ALLEXCEPT, % of Total = var countResponse = COUNTROWS('Responses') var allResponsesForQuestion = CALCULATE(COUNTROWS('Responses'), ALLEXCEPT(Responses,Responses[Question])) RETURN DIVIDE(countResponse, allResponsesForQuestion)
Amazing guide, thank you! Let's say I send out a monthly anonymous team survey with 10 questions similar to your example. How can I compare responses from previous surveys with the same questions, so that if we look a year back we can see the difference in responses month by month?
@@Denis-iq1kj You would use a date table for this! I have a video in the channel with a great date table template that explains how to get it connected (it’s a recent one if you sort on recent); you’d relate it to your survey date column (make sure its date type not date time). Then you should be able to drop your response values into the legend, and a count of responses into the y-axis, using the stacked bar chart visual, use the question as a small multiple, and add month or quarter or whatever to the x-axis. That’ll get you a chart for each question by month. You can even hierarchy it and drop in year too. If you want actual percentage change, you can use relative months as expression filters in the date table (eg last month is -1, month before that is -2), or use the time intelligence functions for the periods you want to compare and divide.
Multi-value fields are a bit of a pain in Power BI, no matter where they're coming from. You essentially have to make a separate query for them that just has the multivalue field and the response ID as columns, then right click on the comma-separated field -> split -> to new rows on the comma. That will give you a value per with the ID still attached. Then you can relate that table to your primary table on the response ID, set the crossfilter direction to "both" to get the filters going in the right direction, and THEN you can count them (I'd do a measure for a count of rows in that related table).
@ oh, do you have a bunch? If you get all the multivalue columns in one query I think it should work to unpivot the question first, then split the values column to new rows. So just one new table.
Yeah, you could do a single select slicer and just drop the question field in there. That would filter the responses by question. Is that what you mean?
How about with filtering and slicing for a specific responses such as responses from specific city? Is there any way to go around with it? Thank you in advance for the possible favorable response.
Yeah, if you're not familiar with dimensional data modeling in Power BI definitely watch a video or two on that - you can use any attribute linked to your surveys or users as a filter. I don't know if your city is entered as a survey question or if it's part of your user dimension - if it's a survey question, you just leave it in as one of the columns in your survey table (not responses) and drop it in a slicer. If it's in your user dimension, you'd relate your user dimension table to your surveys table on the email address of the user (make sure to match case on email addresses, the relationship is case-sensitive) and use it in a filter from there. Relationships are super critical to learn in Power BI, they're where all the magic happens.
Thank you What if I have multiple selection in the columns separated by semicolon, is Split in to rows will help, I analyzing stack overflow data set survey for 2023 and trying to solve these Columns but the data size increases dramatically, any suggestions Thanks
Anytime you have multivalue columns it adds a lot to the complexity. I usually put the multivalue column and the row ID into its own table, then split to new rows, and relate the two tables on the ID field. You may need to set the relationship to bidirectional depend on what you're visualizing. "Big" is relative in Power BI - it's able to handle millions of rows with no problems as long as you model it cleanly. Particularly with the multivalue field data, where those values tend to be basically the same values repeated lots of times (as opposed to paragraphs of unique text from open text fields), it usually doesn't actually take up much space at all in the model. It's the unique text that will really bog it down
Really helpful ! Thanks ! Now... how can we apply this when instead of multiple choices microsoft form only, you have a survey with a list of rows (A, B, C...) with a satisfaction rate in column (very satisfied to very unsatisfied) ?
You mean the Likert type? It depends how you want to display it. It puts each rating statement into a column, so when you unpivot like this it should work the same way - it'll essentially treat each row in the Likert box as its own question. The big thing you'll run into is making sure they sort the way you want them to sort - in this case it might be easiest to add a conditional column before the unpivot step that puts a number in front of the text label so that when you alpha-sort it goes in the right order (e.g. 1 - very unsatisfied, 2 - unsatisfied, 3 - neutral etc). You could also leave it in the main table instead of unpivoting and use the "sort on another column" feature, but then you'd need a separate visual to display it. Sorting things is always a pain, it's a thing. :/
This was a great Video. Thanks for uploading it. I am still new to Power BI but wanted to know if it was possible to set this up a template or model for future surveys? I'd like to spend time positioning graphs and charts, but can it be re used for a new survey. It would be the same exact one, but new answers from say a different department. Or will the fact that we need to create a new non table version of the data cause issues? Thank you.
How you set it up depends on how much manual intervention you're ok with. The least intervention would be the SP folder connector for Power BI, where it will pull all files in a folder. You can add filters to that for the file type, path, naming convention (e.g. contains "survey"). As long as you're using "unpivot other" and selecting the fields that will be the same every time when you unpivot the rest, it will be able to pull in all the data into one table. You'd have the filename associated with the survey, which should have the survey name in it to help tell responses apart. If you wanted to transform that to be a bit friendlier format you could split off the extension or something in the query editor.
Hi, this was fantastic and exactly what I was after. I was wondering is the '% of Total' measure. It is possible to reference to multiple columns than just questions. For e.g. to find the % of total by the country, age and gender a respondent is from? Thank you :)
Yeah totally! How you do it depends on how you want to measure it - e.g. if you do by country, do you want the percentages to add up to 100 for each country, or do you want it to be a percentage of the total responses (where the percentages between all countries adds up to 100)? If the country is one of your survey questions, or are related to your users in some way, you can get the latter by just dropping the dimension into the matrix above the response, and expanding down (or use small multiples in a bar chart, those are good too). If its the former, you can make a new measure that measures for the selected country like this: % of Total by Country = var countResponse = COUNTROWS('Responses') var allResponsesForQuestion = CALCULATE(COUNTROWS('Responses'), ALL(Responses), 'Responses'[Question] = SELECTEDVALUE('Responses'[Question]), 'Submitted Surveys'[Country] = SELECTEDVALUE('Submitted Surveys'[Country])) RETURN DIVIDE(countResponse, allResponsesForQuestion) You could also put the country at the top of the rows, above the question, if you wanted to group everything up on country - just depends how you want to see it. E.g. christine-payton.com/wp-content/uploads/2024/07/Screenshot-2024-07-31-094219.png vs christine-payton.com/wp-content/uploads/2024/07/Screenshot-2024-07-31-094407.png This would be one of those situations where field parameters and small multiples bar chart would really shine, too.
@@bi-ome Hi, thank you for getting back to me so quickly. I would like the percentages between all the countries to add up to 100. Actually I did change the measurement to how you have shown and it worked. But I was after a combination of demographical fields; including gender, age, country etc in the one matrix by % of total? When I included more fields than the 'country' field as you have shown above the % of total didn't work. I have an example of what I am trying to achieve, if I am able to email it to you, that would helpful :)
I know this comment is completely out of context, but have you by any chance experienced issues with the new February version related to the message 'Cube Table must have exactly one island'?
No, I haven't - it looks like it's definitely a thing others are running into though, from this thread: community.fabric.microsoft.com/t5/Power-Query/Power-BI-error-quot-Cube-Table-must-have-exactly-one-island-quot/td-p/3089145
Dear Cristine, I hope you are doing well, need one help in one of my power App Project. where I want to provide a user to 1- Download a Excel template 2 Fill the template 3 User should be able upload the template using power App. 4 and patch the records to database MS lists I found 2 Tutorial on TH-cam but it confusing to me please check if you can help me with this requirement it will be great help. Tutorial Link : 1- th-cam.com/video/EHONWkYQNGA/w-d-xo.html 2- th-cam.com/video/s5dV_nSUBDY/w-d-xo.html
I think you probably want to use a Library instead of a List, if the record is Excel files. Libraries have the concept of file templates, that users can create files from via the +new menu, so you might not even need a Power App? They wouldn't have to download it, they'd just create the file from the template via the UI and edit it right in SharePoint. But, I have a tendency to not use Power Apps unless it's absolutely necessary, so I WOULD say that lol. (support.microsoft.com/en-us/office/set-a-custom-template-for-a-sharepoint-library-8e0177eb-46bd-4346-9998-330c32733c25)
Someone requested the files for this video - here's the github link (this is the version in the intro, I happened not to save the exact one in the walkthrough - they're very close though): github.com/chpayton/BI-Files/tree/main/Survey%20Data
Thanks! I was serious about the coffee. May your day be filled with pretty moments.
Thanks you! You too, best of luck on your work :)
One more awesome tutorial. Thank you for teaching us.
So quick and easy! Thanks Christine 🙂
You bet!
THANK YOU for the full walk through. I can't tell you how hard this is to find...
This was outrageously helpful, thank you!
So glad!
Nice one.
Bit of a few codes with ALLEXCEPT,
% of Total =
var countResponse = COUNTROWS('Responses')
var allResponsesForQuestion = CALCULATE(COUNTROWS('Responses'), ALLEXCEPT(Responses,Responses[Question]))
RETURN DIVIDE(countResponse, allResponsesForQuestion)
Thanks @christine very beneficial
Very helpful - thank you
Amazing guide, thank you! Let's say I send out a monthly anonymous team survey with 10 questions similar to your example. How can I compare responses from previous surveys with the same questions, so that if we look a year back we can see the difference in responses month by month?
@@Denis-iq1kj You would use a date table for this! I have a video in the channel with a great date table template that explains how to get it connected (it’s a recent one if you sort on recent); you’d relate it to your survey date column (make sure its date type not date time). Then you should be able to drop your response values into the legend, and a count of responses into the y-axis, using the stacked bar chart visual, use the question as a small multiple, and add month or quarter or whatever to the x-axis. That’ll get you a chart for each question by month. You can even hierarchy it and drop in year too.
If you want actual percentage change, you can use relative months as expression filters in the date table (eg last month is -1, month before that is -2), or use the time intelligence functions for the periods you want to compare and divide.
how does this work with multiple choice questions devided by commas?
Multi-value fields are a bit of a pain in Power BI, no matter where they're coming from. You essentially have to make a separate query for them that just has the multivalue field and the response ID as columns, then right click on the comma-separated field -> split -> to new rows on the comma. That will give you a value per with the ID still attached. Then you can relate that table to your primary table on the response ID, set the crossfilter direction to "both" to get the filters going in the right direction, and THEN you can count them (I'd do a measure for a count of rows in that related table).
@ so in short: I need a new table for every question?😅
@ oh, do you have a bunch? If you get all the multivalue columns in one query I think it should work to unpivot the question first, then split the values column to new rows. So just one new table.
@@bi-omeI have around 100 questions all with 3-5 possible multiple choice answers from a survey
Hi, is there a way that I can use a slicer so my visuals will show the response for each question one at a time?
Yeah, you could do a single select slicer and just drop the question field in there. That would filter the responses by question. Is that what you mean?
How about with filtering and slicing for a specific responses such as responses from specific city? Is there any way to go around with it? Thank you in advance for the possible favorable response.
Yeah, if you're not familiar with dimensional data modeling in Power BI definitely watch a video or two on that - you can use any attribute linked to your surveys or users as a filter. I don't know if your city is entered as a survey question or if it's part of your user dimension - if it's a survey question, you just leave it in as one of the columns in your survey table (not responses) and drop it in a slicer. If it's in your user dimension, you'd relate your user dimension table to your surveys table on the email address of the user (make sure to match case on email addresses, the relationship is case-sensitive) and use it in a filter from there. Relationships are super critical to learn in Power BI, they're where all the magic happens.
Thank you
What if I have multiple selection in the columns separated by semicolon, is Split in to rows will help, I analyzing stack overflow data set survey for 2023 and trying to solve these Columns but the data size increases dramatically, any suggestions
Thanks
Anytime you have multivalue columns it adds a lot to the complexity. I usually put the multivalue column and the row ID into its own table, then split to new rows, and relate the two tables on the ID field. You may need to set the relationship to bidirectional depend on what you're visualizing.
"Big" is relative in Power BI - it's able to handle millions of rows with no problems as long as you model it cleanly. Particularly with the multivalue field data, where those values tend to be basically the same values repeated lots of times (as opposed to paragraphs of unique text from open text fields), it usually doesn't actually take up much space at all in the model. It's the unique text that will really bog it down
Really helpful ! Thanks ! Now... how can we apply this when instead of multiple choices microsoft form only, you have a survey with a list of rows (A, B, C...) with a satisfaction rate in column (very satisfied to very unsatisfied) ?
You mean the Likert type? It depends how you want to display it. It puts each rating statement into a column, so when you unpivot like this it should work the same way - it'll essentially treat each row in the Likert box as its own question. The big thing you'll run into is making sure they sort the way you want them to sort - in this case it might be easiest to add a conditional column before the unpivot step that puts a number in front of the text label so that when you alpha-sort it goes in the right order (e.g. 1 - very unsatisfied, 2 - unsatisfied, 3 - neutral etc). You could also leave it in the main table instead of unpivoting and use the "sort on another column" feature, but then you'd need a separate visual to display it. Sorting things is always a pain, it's a thing. :/
This was a great Video. Thanks for uploading it. I am still new to Power BI but wanted to know if it was possible to set this up a template or model for future surveys? I'd like to spend time positioning graphs and charts, but can it be re used for a new survey. It would be the same exact one, but new answers from say a different department. Or will the fact that we need to create a new non table version of the data cause issues? Thank you.
How you set it up depends on how much manual intervention you're ok with. The least intervention would be the SP folder connector for Power BI, where it will pull all files in a folder. You can add filters to that for the file type, path, naming convention (e.g. contains "survey"). As long as you're using "unpivot other" and selecting the fields that will be the same every time when you unpivot the rest, it will be able to pull in all the data into one table.
You'd have the filename associated with the survey, which should have the survey name in it to help tell responses apart. If you wanted to transform that to be a bit friendlier format you could split off the extension or something in the query editor.
Hi, this was fantastic and exactly what I was after. I was wondering is the '% of Total' measure. It is possible to reference to multiple columns than just questions. For e.g. to find the % of total by the country, age and gender a respondent is from? Thank you :)
Yeah totally! How you do it depends on how you want to measure it - e.g. if you do by country, do you want the percentages to add up to 100 for each country, or do you want it to be a percentage of the total responses (where the percentages between all countries adds up to 100)?
If the country is one of your survey questions, or are related to your users in some way, you can get the latter by just dropping the dimension into the matrix above the response, and expanding down (or use small multiples in a bar chart, those are good too). If its the former, you can make a new measure that measures for the selected country like this:
% of Total by Country =
var countResponse = COUNTROWS('Responses')
var allResponsesForQuestion = CALCULATE(COUNTROWS('Responses'), ALL(Responses), 'Responses'[Question] = SELECTEDVALUE('Responses'[Question]), 'Submitted Surveys'[Country] = SELECTEDVALUE('Submitted Surveys'[Country]))
RETURN DIVIDE(countResponse, allResponsesForQuestion)
You could also put the country at the top of the rows, above the question, if you wanted to group everything up on country - just depends how you want to see it. E.g. christine-payton.com/wp-content/uploads/2024/07/Screenshot-2024-07-31-094219.png vs christine-payton.com/wp-content/uploads/2024/07/Screenshot-2024-07-31-094407.png
This would be one of those situations where field parameters and small multiples bar chart would really shine, too.
@@bi-ome Hi, thank you for getting back to me so quickly. I would like the percentages between all the countries to add up to 100. Actually I did change the measurement to how you have shown and it worked. But I was after a combination of demographical fields; including gender, age, country etc in the one matrix by % of total? When I included more fields than the 'country' field as you have shown above the % of total didn't work. I have an example of what I am trying to achieve, if I am able to email it to you, that would helpful :)
I know this comment is completely out of context, but have you by any chance experienced issues with the new February version related to the message 'Cube Table must have exactly one island'?
No, I haven't - it looks like it's definitely a thing others are running into though, from this thread: community.fabric.microsoft.com/t5/Power-Query/Power-BI-error-quot-Cube-Table-must-have-exactly-one-island-quot/td-p/3089145
Dear Cristine,
I hope you are doing well,
need one help in one of my power App Project. where I want to provide a user to
1- Download a Excel template
2 Fill the template
3 User should be able upload the template using power App.
4 and patch the records to database MS lists
I found 2 Tutorial on TH-cam but it confusing to me please check if you can help me with this requirement it will be great help.
Tutorial Link :
1- th-cam.com/video/EHONWkYQNGA/w-d-xo.html
2- th-cam.com/video/s5dV_nSUBDY/w-d-xo.html
I think you probably want to use a Library instead of a List, if the record is Excel files. Libraries have the concept of file templates, that users can create files from via the +new menu, so you might not even need a Power App? They wouldn't have to download it, they'd just create the file from the template via the UI and edit it right in SharePoint. But, I have a tendency to not use Power Apps unless it's absolutely necessary, so I WOULD say that lol. (support.microsoft.com/en-us/office/set-a-custom-template-for-a-sharepoint-library-8e0177eb-46bd-4346-9998-330c32733c25)
@@bi-ome Thank you very much for your response. have a great day.
Thank you so much, this is something that I was really looking for. Cheers !!