Search code examples
excelpivotpivot-tablepivot-chart

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

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.

Solution

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

    enter image description here

    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

    enter image description here

    and the graph looks like this

    enter image description here

    Here is the pivot options window

    enter image description here