Dynamic Scatter chart based on criteria

[Danalope23]

Hello,

I want to create a X/Y scatter chart to plot projects Final Amounts Invested amounts & % and use slicers to select one or more than one location.
I have numerous locations and each location has a different number of projects to be included.
This number of projects changes each month
Data source is a pivot table
I want each location to be a series with a different color in the scatter chart. I want the color to stay with the series when slicers are used to select a particular location.
I attempted to create a dynamic range based on a Location criteria and then use this range for each location in my scatter chart but I cannot figure out the offset & index formula
See the image. Imagining that the table is a Pivot, what is the formula to use in Series X values & series Y values so that if there are more or less data for each location, the range is dynamic?
I do not want to manually adjust my ranges for each location every month.

Do I need to create Names range with offset based on a criteria? I cannot figure out how to do so and then use this range in the scatter graph.

Thank you for your help.
Annie