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)
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 :)
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?
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
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)
![[TH] 2024 PMSL SEA W2D1 | Fall | ต่อเนื่อง ในสัปดาห์สอง](http://i.ytimg.com/vi/ElcO9uC_Dbk/mqdefault.jpg)
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 :)
This was outrageously helpful, thank you!
So glad!
THANK YOU for the full walk through. I can't tell you how hard this is to find...
Thanks @christine very beneficial
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)
So quick and easy! Thanks Christine 🙂
You bet!
One more awesome tutorial. Thank you for teaching us.
Very helpful - thank you
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 :)
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?
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
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 !!