Pivot tables and Slicers

[Kellym923]

I'm trying calculate occupancy rate in a pivot table, when remove an agent from a slicer the occupancy rate is not going up, rather it's going down, seems that it's not maintaing the over all work load and when I remove an agent it is also removing the workload associated with that agent, I'm trying to determine how much of increase I will have by removing an agent, does anyone how I can fix this? Thanks for any help or direction

kelly

 

[JamesCanale]

I didn't use a pivot table, because I hate them, and 365 is good without them. I tie a slicer to a table, and add a 'visible?' column to it with the Aggregate function that is set to ignore hidden values. Anyway, it only counts visible agents, but keeps the total hours. Is this along the lines of what you are looking for?

MrExcelPlayground24.xlsx
	A	B	C
1	Agent	Workload	visible?
2	Jamie	100	1
3	Fred	90	1
4	Harry	80	1
5	Mary	70	1
6	Sally	60	1
7
8
9
10		All Hours	5
11		400	Jamie
12			Fred
13		Occupancy	Harry
14		80.0	Mary
15			Sally
Sheet9

Cell Formulas
Range	Formula
C2:C6	C2	=AGGREGATE(3,5,[@Agent])
C10	C10	=SUM(Table5[visible?])
B11	B11	=SUM(Table5[Workload])
C11:C15	C11	=FILTER(Table5[Agent],Table5[visible?])
B14	B14	=B11/C10
Dynamic array formulas.