• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Structure Pivot Table when Data has 1s and 0s from Survey Questions|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / How to Structure Pivot Table when Data has 1s and 0s from Survey Questions|Dashboards & Charts|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsHow to Structure Pivot Table when D…
sp_PrintTopic sp_TopicIcon
How to Structure Pivot Table when Data has 1s and 0s from Survey Questions
Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
1
November 13, 2021 - 11:53 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 13, 2021 - 12:28 pm
sp_Permalink sp_Print

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

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
3
November 16, 2021 - 2:32 am
sp_Permalink sp_Print

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.

  1. 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?
  2. 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)?
  3. 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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 17, 2021 - 5:16 pm
sp_Permalink sp_Print

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

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
5
November 20, 2021 - 5:21 am
sp_Permalink sp_Print

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 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
November 20, 2021 - 2:30 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
7
November 30, 2021 - 12:31 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
November 30, 2021 - 10:16 am
sp_Permalink sp_Print

Yes, your data should always be stored in a Tabular Layout. From there everything is easy.

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
9
December 1, 2021 - 6:29 am
sp_Permalink sp_Print

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?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
December 1, 2021 - 7:50 am
sp_Permalink sp_Print

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. 

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
11
December 17, 2021 - 7:26 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
December 17, 2021 - 3:03 pm
sp_Permalink sp_Print

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.

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
13
December 17, 2021 - 11:36 pm
sp_Permalink sp_Print

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 

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
14
December 18, 2021 - 6:58 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
15
December 20, 2021 - 9:37 am
sp_Permalink sp_Print

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

Avatar
Melissa Watson
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 13, 2021
sp_UserOfflineSmall Offline
16
December 22, 2021 - 2:35 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
17
December 23, 2021 - 12:07 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
18
December 23, 2021 - 8:53 pm
sp_Permalink sp_Print sp_EditHistory

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

 

Survey Data Workbook

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
19
December 23, 2021 - 10:00 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Riny van Eekelen, Kumud Patel, Bright Asamoah
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.