PivotChart with Slicer

I have recently started working with Pivot Tables and Pivot Charts in order to visualize the data. I have my data and I am trying to create a column graph that shows the name on the X axis and count of how many on the Y axis. I would like to be able to use a slicer in order to visualize the years, 2021 and 2022 separately, while seeing only the grand total for each name in the graph.

I can create a graph with the grand total, but I don't seem to have the option of creating a slicer for the years, and if I can create a slicer with the years, they show up separately as two columns for each name. Is there a graph type that I am not seeing or a better way to have the graph only show the grand total, and still allow me to visualize the subtotals for the years if I would like?

The attached workbook has the two column graph, but the slicer that I am looking for.

Any help is appreciated!

Hi,

I have created a chart by making a table below the pivot table with name and grand total as columns. check if this satisfies your requirement.

Thank you so much for your reply. I have tried this on my data set, and this does solve what I was asking, however, I realized an issue that I didn't include in my original test data. Some names do not have data for both years, and this is causing some issues with the graphing.

When the slicer is chosen, it changes the table so then the references are updated and "Grand Total" is able to be seen with no values, and any Names not present are shown as a "0" column.

I have updated my test data with this new change so it can be visualized.

Hi,
I have added a separate pivot table for names, to display all the names at all times.
Also I have changed the formula and if the name is not present in the pivot the total will be blank against that name.
Check if this satisfy your requirement.

Thank you very much for the reply again. Your solution does show a graph for what I am looking for. I appreciate your input into my problem, as this has been very helpful.

The data that I am working with is currently static based off of a singular export, but if I may pick your brain one additional time, would you have any thoughts on using this set up with dynamic data?

For example if additional Names were added into the data table? I understand that the pivot tables could be refreshed, but as the graph is based off of the values in a created array, they would remain unchanged when the pivot tables have adjusted for the new values.

Thank you for the reply. That does simplify things a bit and still give me the graph I am interested in.

I have really appreciated all of the advice given as it has helped me learn a lot more about Pivot tables and graphs.

I have done the same. Created a chart by using the table the pivot table - works well.