Filter Formula & Data Points

[mousetap]

Hi,
I am seeking assistance for two things.

1. Is there a way to work around FILTER formula when the filtering criteria are linked to report filters on a pivot table? It does work when I do set the report filters to single items. It doesn't work if the report filter contains multiple items. Ex. in my attached example, when the Team filter(J2) is selected with multiple items, I don't get any results.

2. Is there a way to get the results off from a data point of a pivot chart when clicked on that specific data point? I have the raw data of names, their active statuses, teams and graduation dates listed in a table. I used a pivot table to filter the data to find out how many will graduate by a specific date That data is show cased in a pivot chart.

I wanted to create a list, when someone clicked on a data point. It will list out the names of student(s) who will graduate on that selected date. Ex: If I select data point for April 2024, it should list Brad K, Calvin H, Bret H below cell L29. It would be nice if I can utilize this on other charts as well.

I would really appreciate any assistance on these two questions. Sample file is attached.

 

[p45cal]

In the attached, the table at cell A28 is a pivot table.
I've used slicers to connect that pivot and your pivot.
I've added a column to your source data (this can be done in the background (Power Pivot) if necessary) because you've grouped Grad Date in yours and I can't ungroup to get raw dates without also ungrouping your pivot.

I've not addressed your point 2. I'll have an explore and if I can come up with something I'll post again.

edit:
I've attached a second file (name ends Help-2.xlsx) where the only difference is that the new pivot table is sorted by grad date. More involved than you'd think as I needed to add Grad date2 to the Values area of the new pivot, and it becomes less easy to hide the field headers, so I've hidden row 28 altogether. This allows me to sort on those dates.

 

[mousetap]

In the attached, the table at cell A28 is a pivot table.
I've used slicers to connect that pivot and your pivot.
I've added a column to your source data (this can be done in the background (Power Pivot) if necessary) because you've grouped Grad Date in yours and I can't ungroup to get raw dates without also ungrouping your pivot.

I've not addressed your point 2. I'll have an explore and if I can come up with something I'll post again.

edit:
I've attached a second file (name ends Help-2.xlsx) where the only difference is that the new pivot table is sorted by grad date. More involved than you'd think as I needed to add Grad date2 to the Values area of the new pivot, and it becomes less easy to hide the field headers, so I've hidden row 28 altogether. This allows me to sort on those dates.

Hi p45cal,
Thank you very much for providing an answer to my question. Using the slicers was a great idea. Also, by looking at how you presented the pivot table, gave me a chance to explore an area (report layout) that I haven't explored in the past. Looking forward to your solution to the 2nd part of the question. (only if you were able to... )

Thanks again...

 

[p45cal]

Looking forward to your solution to the 2nd part of the question.

More awkward than I thought. See attached.
Macros need to be enabled.
The list produced will depend on how you have the pivot table at cell I4 displaying. I've left it with 2024 collapsed, the other years expanded. This is reflected in the chart; 7 students for 2024. Click on that point in the chart to get the list and you get only a year showing in cell L26, click on another point and the month and year will show in that cell.
There's lots that might make it go wrong. If the chart stops responding to clicks then you can try running the macro blah to get it going again, otherwise, close the file and re-open.
There's plenty more to explain and I will do so if this a direction you want to go in.