Hi Guys,
I have been searching around for an answer to this for a couple of hours and struggling to find a solution.
I would like to add a dynamic mean line into a pivot table/chart dependent on which department and month I am looking at.
Source data is set out like this where I have a formula to calculate the average:
<tbody>
[TD="width: 125, bgcolor: transparent"] 10
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Two
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 15
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 150
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] One
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 15
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Two
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 10
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 30
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 40
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
</tbody>
With the pivot table data currently looking like this with slicers for Name, Department and Month:
<tbody>
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 125, bgcolor: transparent"] 12
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 125, bgcolor: transparent"] 150
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
</tbody>
What I would like the pivot table to do is adjust theaverage/mean dependant on which slicer items are selected i.e.
Slicer Selections
Dept: AA
Month: January
Name: One, Two
Pivot would show
<tbody>
[TD="width: 111, bgcolor: transparent"] 12.5
[/TD]
</tbody>
Thanks in advance
Stuart
I have been searching around for an answer to this for a couple of hours and struggling to find a solution.
I would like to add a dynamic mean line into a pivot table/chart dependent on which department and month I am looking at.
Source data is set out like this where I have a formula to calculate the average:
Name | Dept | Month | Volume | Average |
One | AA | January | ||
<tbody>
[TD="width: 125, bgcolor: transparent"] 10
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Two
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 15
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] January
[/TD]
[TD="width: 125, bgcolor: transparent"] 150
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] One
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 15
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Two
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 10
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 119, bgcolor: transparent"] AA
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 15
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 30
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 40
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 119, bgcolor: transparent"] BB
[/TD]
[TD="width: 125, bgcolor: transparent"] February
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 40
[/TD]
</tbody>
With the pivot table data currently looking like this with slicers for Name, Department and Month:
Name | Volume | Average |
One | 10 | 25 |
Two | 15 | |
<tbody>
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Three
[/TD]
[TD="width: 125, bgcolor: transparent"] 12
[/TD]
[TD="width: 111, bgcolor: transparent"] 25
[/TD]
[TD="width: 122, bgcolor: transparent"] Four
[/TD]
[TD="width: 125, bgcolor: transparent"] 20
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Five
[/TD]
[TD="width: 125, bgcolor: transparent"] 50
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
[TD="width: 122, bgcolor: transparent"] Six
[/TD]
[TD="width: 125, bgcolor: transparent"] 150
[/TD]
[TD="width: 111, bgcolor: transparent"] 73
[/TD]
</tbody>
What I would like the pivot table to do is adjust theaverage/mean dependant on which slicer items are selected i.e.
Slicer Selections
Dept: AA
Month: January
Name: One, Two
Pivot would show
Name | Volume | Average |
One | 10 | 12.5 |
Two | 15 |
<tbody>
[TD="width: 111, bgcolor: transparent"] 12.5
[/TD]
</tbody>
Thanks in advance
Stuart