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

AsadHabib

New Member
Joined
Dec 8, 2018
Messages
1
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top