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