Getting Pivot Chart to change yearly goals into month according to filtered team

[AsadHabib]

Hi Everyone,

I have a data set of sales reps and their sales (date of sale, amount, team, etc). I also have a separate sheet which has goals for these folks for the full year.

I've setup a pivot table and a line chart on the sales file that shows total sales for the year broken down by months and can also be filtered per team using slicers. Now here's where i'm stuck:

I can't think of any way to incorporate the monthy goals for these folks into the data in a way that will allow me to see on the pivot chart how they are doing compared to their goals. As each line in my raw data represent one sale, reps can have more than one sale per month or even none. I want to be able to user slicers and see goals change according to the team as well. So if i choose team b, the pivot chart (which shows sales by month) should show a line for monthly sales by that team and another line that shows the goal target for the reps of team b for each month. Now you can easily compare goals vs progress each month for that team.

Is there a way to make this work? Any help would be appreciated!

Thanks,

Asad