Create a Pivot Chart which compares an individual's score with that of his teams Average score

Question

I need to compare how Fred is performing compared to the rest of his team Team 1 in the form of Pivot Line Graph. Below is the data.

A         B         C        D
James     Team 1    20   11/05/18
Fred      Team 1    50   12/11/18
Elliott   Team 2    100  21/04/18
Sarah     Team 1    75   11/04/18
Fred      Team 1    75   11/04/18

I need to create a Pivot Table from this data and then a Pivot Chart which shows a line graph for Team 1 and a line graph overlaid for Fred. The Team scores would be the average i.e. total of C Team As / No. of Team As.

The chart would have slicers attached to other Charts where a different Name or possible Team could be selected to filter.

I can create a chart for the names or for the teams but weighing a name against the team aggregate doesn't look possible.

Answer 1

Ok here it is, let me know if it solves your problem

Formula in E2 is

=AVERAGE(IF($B$2:$B$26=B2,IF($C$2:$C$26=C2,$D$2:$D$26)))

It is an array formula and have to be entered using Ctrl+Shift+Enter

The pivot table is as below

and the graph looks like this

Here is the pivot options window