Log In
Register
Home
November 13, 2021
Offline
Hello,
I was following this Youtube tutorial on How to build Interactive Excel Dashboards and ran into a problem when constructing the pivot tables. The data I have is from a survey and is structured to capture 1s for "yes", and 0s for "no" or no response. I am trying to create a pivot table that displays the number of "yes" for each question and the percentage of "yes" out of the total number of responses. I also want the pivot table to update as additional responses are entered.
Because my rows are full of 1s and 0s from my data, I can only put the data I'm trying to capture in the Values field. When I try to put it in the Columns or Rows, the pivot table doesn't make visual sense. I believe the YouTube tutorial did not totally help me because my data structure did not match that of the video. Do I need to restructure my data (I really hope not) or am I not using the pivot fields correctly?
How do I create an interactive pivot table that displays the total responses and percentage of "yes" for each question asked when the data uses 1s and 0s? Really hope someone can help me with this. Thanks!
Lissa
July 16, 2010
Offline
Hi Melissa,
Yes, you need to unpivot your data, but this is easy with Power Query: https://www.myonlinetraininghu.....ry-unpivot
You can then use Show Values as > Percentage of Parent Column Total. See the Data sheet of the file attached.
Mynda
November 13, 2021
Offline
Hello Mynda,
Thanks for your response and for making me aware there is a forum. I'm not good with Excel so this a very helpful. I reviewed your response and was not able to follow the post but was able to locate one of your YouTube tutorials and follow that a bit better. I'm hoping you can help me clarify a few things.
- When I click on the pivot table from your attachment, I am not able to view the pivot fields so I can see how structured the data within the fields. How can I see that?
- Does the power query link to the data source so as I entered additional survey responses the power query and any create pivot charts will update (trying to create a dynamic dashboard)?
- I'm really looking to capture the "yes" responses for each question (billboard, television, DDS, etc.). I guess as a default, the survey questions that did not receive a response of "yes" are "no" which I had to enter so there are no empty data cells. The "no" responses are not what I'm after. I'm trying to capture the total "yes" responses for each option as well as the percentage of "yes" out of the total. How do I restrict the pivot chart to display this when there is "no" in the data source?
Melissa
July 16, 2010
Offline
Hi Melissa,
1. To view the field list, right-click the PivotTable > Show Field List
2. Yes, just click the Refresh All button on the Data tab to pick up new data. However, you need to load the data direct to the PivotTable, rather than to a table and then create a PivotTable as it currently is in my previous file. I only did this so you could see the difference in the data format in the worksheet. To change the Load to Settings, open the Queries and Connections pane (data tab) > right-click the query > Load to > PivotTable.
3. You would have to use Power Pivot for this and write a DAX measure for the Yes as a % of Total Answers. See file attached on the PivotTables sheet. The second PivotTable is a Power Pivot PivotTable. If you use Power Pivot then you load the Power Query directly to Power Pivot and refresh using Refresh All.
There's a lot to learn here and not something I can teach in the forum. If you'd like to learn Power Query and Power Pivot, please consider my courses linked to.
Mynda
November 13, 2021
Offline
Hi Mynda,
I'm a bit unclear with this response regarding adding additional survey data.
"...you need to load the data direct to the PivotTable, rather than to a table and then create a PivotTable..."
When following your tutorial, you advise that when updating the data source, the pivot tables and dashboards will update when refreshed. Why then would I have to add new data directly to the Pivot Table instead of the data source? Is it because of the added Power Query?
In my opinion, this is a very simple survey and I was certain I could easily create a dynamic dashboard as my data isn't as complex as your tutorial. I wonder if it's becoming complex to create a dashboard because of the way I captured the data source. Is there a better way for me to structure my survey data source that would make creating a dashboard less complex? If so, can you please advise on a better way? Perhaps the end of this project is feeling impossible because I made the wrong start.
Please help me.
Melissa
July 16, 2010
Offline
Hi Melissa,
I doubt you can collate your data in a tabular layout from the get go, as it probably comes from a survey system that puts it in this format. I should have been clearer on my second point. Because your data is not in a tabular format, you need to get the data from your Excel file and use Power Query to unpivot it. After you unpivot it in Power Query, you 'close & load to' and at this dialog box you should choose two options: Only create connection and Load to data model.
To update the dashboard you'll simply add/replace the data (not the headers) of the original source data table.
There is a lot to learn here and it's not something that can really be covered in a forum. You might like to consider my Power Query course and Power Pivot course.
Mynda
November 13, 2021
Offline
Hi Mynda,
No, the data does not come from a survey system that puts it in this format. I created the format and I enter the survey results manually. So, in your professional opinion, is there a better way to have formatted the data that would make creating a dashboard following your tutorial much easier?
Melissa
July 16, 2010
Offline
Yes, your data should always be stored in a Tabular Layout. From there everything is easy.
November 13, 2021
Offline
How? Is it not tabular? How else would you lay out yes and no responses that's easier to work with aligning with your tutorial?
July 16, 2010
Offline
Answers should be in one column and the category in another. Not a separate column for each category. If you look at the query editor you'll see the data in the correct tabular layout.
November 13, 2021
Offline
Hi Mynda,
Your Response
"I doubt you can collate your data in a tabular layout from the get go, as it probably comes from a survey system that puts it in this format. I should have been clearer on my second point. Because your data is not in a tabular format, you need to get the data from your Excel file and use Power Query to unpivot it. After you unpivot it in Power Query, you 'close & load to' and at this dialog box you should choose two options: Only create connection and Load to data model."
I've done this and I see the table name in the Queries & Connections pane. The data source, however, looks the same. So from this point, do I proceed to create the pivot tables as directed in the video?
To add new responses, do I add them to the data source as it appears? I'm guessing the table in the Queries & Connections pane will pick up that data and apply it to the pivot table?
Melissa
July 16, 2010
Offline
If you've got your data in a tabular layout then you can go ahead and create the PivotTables. You can add new responses to the original source data table, then refresh the queries and PivotTables and the dashboard will update.
November 13, 2021
Offline
Hi Mynda,
As a reminder, I don't have a vast knowledge of Excel so what's obvious to you might be unclear to me.
Here's what I'm trying to understand. When watching the tutorial on unpivoting data, the unpivoted data was placed on the same sheet as the source data which made the unpivoted data visible (Yes, I understand it was only placed there for viewer learning). With your directive to select 'Only Create a Connection", I did not realize the unpivoted data would not be visible. Not a huge problem but not understanding Excel, I'm not sure where the data is positioned for me to proceed with creating pivot tables following the next tutorial.
I see that the unpivoted table landed in the Queries & Connections pane. So when I go to Summarize with Pivot Table, Excel will use the unpivoted data from the Queries & Connections pane and not the visible data from the original data source, correct?
So I'm seeing the original data source and it's there for me to add new data but my pivot tables, slicers, etc. that will be created following the tutorial, will be pulling from the unpivoted data within the Queries & Connections pane. All I need to do is right-click > Refresh from the Queries & Connections pane and that will update my tables within the dashboard, correct?
Melissa
November 13, 2021
Offline
Also, I'm trying to group the Age and getting an error message "cannot group that selection." Why might that be (this is so frustrating)?
July 16, 2010
Offline
Hi Melissa,
I appreciate it's not easy to follow written instructions. To clarify, I said: "After you unpivot it in Power Query, you 'close & load to' and at this dialog box you should choose two options: Only create connection and Load to data model."
Then when you insert your PivotTable it should be from the Power Pivot data model as the source. I don't know which version of Excel you're using, so it's difficult to give specific instructions, but you can choose the Data Model as the PivotTable source from the Insert PivotTable drop down or Insert PivotTable dialog box, depending on your Excel version.
If you create your PivotTables from Power Pivot then you can go to the Data tab and Refresh All to update all queries and PivotTables. You can also see the data in the Power Pivot window.
In regards to the age grouping: in Power Pivot you don't have grouping options like we have in regular PivotTables. If you want the ages grouped then you need to add this as a column in your source data (i.e. classify each row into an age group) or use a dimension table for the grouping.
I hope I've provided enough to point you in the right direction. However, I suspect your Excel skills may not be up to the task, and this is not something I can bring you up to speed on in a forum setting. There's just too much to learn about the fundamentals of Power Pivot and building a data model that need to be covered. If you'd like to learn Power Pivot, please consider my Power Pivot course.
Kind regards,
Mynda
November 13, 2021
Offline
Hi Mynda,
I appreciate that you have courses available that teach the various aspects of Excel. I am not a heavy user of Excel as I'm sure you are able to determine. I'm simply working on a project, came across your video on creating dashboards, decided adding a dashboard to this project would be beneficial, and am attempting to incorporate it - not knowing my data would require some additional steps not covered by the video.
I need to complete this project within a few days and taking an entire course to do so isn't feasible for this single project. By the time I get through the course, I would have missed my deadline. I do believe the courses are beneficial for those working in Excel more heavily and consistently; that isn't the case for me (perhaps it will be in the future). I'm simply trying to get through a project and came to this forum for help.
Are you able to assist me with this? I just need help addressing the pivot table so I can continue to the next steps of your video to create the dashboard. You seem to understand exactly what needs to be done to get the pivot table formatted and I'm struggling to understand. Can you please me?
Melissa
July 16, 2010
Offline
Hi Melissa,
I've already given you the solutions to the DAX measure and age band grouping in the file I attached to my reply on November 17.
As you now realize, the requirements for your dashboard are more advanced than what I covered in the dashboard tutorial you have watched. You require calculations that can only be done with Power Pivot DAX measures. Once you start to need DAX it opens a whole level of complexity because you now need to understand how to build a data model and write DAX formulas.
I understand you don't see the value in learning these skills to only use them once, combined with your time constraints. However, with zero knowledge of Power Pivot it becomes a task beyond the purpose of the forum, which is to provide support when people get stuck. What you need is someone to build these reports for you, and that's a job for a consultant.
I can put you in touch with a consultant who can help you, however your deadline might be tricky given it's Christmas around the corner. Let me know if you'd like the contact details for the consultant.
Mynda
VIP
Trusted Members
June 25, 2016
Offline
Hi Melissa
Using the data that Mynda had unpivoted, I created 2 PivotTables.
One shows the total count of responses and another the %.
I have created a table from the data. If you have new data, just add it to the bottom of the table and refresh the PivotTable.
Hope this is what you wanted.
Good luck.
Sunny
July 16, 2010
Offline
Hi Sunny,
I don't think this is what Melissa wanted as she said:
"I'm trying to capture the total "yes" responses for each option as well as the percentage of "yes" out of the total. How do I restrict the pivot chart to display this when there is "no" in the data source?" ...And segmented by age group as per Melissa's example file.
Unless I misunderstood, it's not the percentage of yes answers vs no answers, which can be done with regular PivotTables as you've displayed.
I guess another option might be to remove the No answers from the dataset using Power Query, and then you could use regular PivotTables which would simplify the process.
See example file attached using Power Query to filter out the No answers from the source data and then using regular PivotTables with Show Values As to calculate the percentages.
Mynda
1 Guest(s)
