Hi Melissa,

Yes, you need to unpivot your data, but this is easy with Power Query: https://www.myonlinetraininghub.com/power-query-unpivot

You can then use Show Values as > Percentage of Parent Column Total. See the Data sheet of the file attached.

Mynda

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

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

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 

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

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

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

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?

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. 

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

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.

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 

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

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