I have a table that looks something like
[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Month[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Jan[/TD]
[TD]1075[/TD]
[TD]1500[/TD]
[TD]28%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jan[/TD]
[TD]4109[/TD]
[TD]4950[/TD]
[TD]17%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Feb[/TD]
[TD]1084[/TD]
[TD]4000[/TD]
[TD]73%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Feb[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]56%[/TD]
[/TR]
</tbody>[/TABLE]
The pivot table has a slicer that filters by person which works great when only looking at one at a time. The issue I am having is that if I unfilter and select all on the slicer, I want target and actual to sum everything, but keep variance calculated by target and actual. So in the above example for January, I'm getting a variance of 28% + 17% = 45% rather than the 20% I want it to show. The variance % field is a calculated column in power pivot.
Is there any way for me to have the calculated field calculate on what is shown in the other columns rather than sum everything?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Month[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Jan[/TD]
[TD]1075[/TD]
[TD]1500[/TD]
[TD]28%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jan[/TD]
[TD]4109[/TD]
[TD]4950[/TD]
[TD]17%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Feb[/TD]
[TD]1084[/TD]
[TD]4000[/TD]
[TD]73%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Feb[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]56%[/TD]
[/TR]
</tbody>[/TABLE]
The pivot table has a slicer that filters by person which works great when only looking at one at a time. The issue I am having is that if I unfilter and select all on the slicer, I want target and actual to sum everything, but keep variance calculated by target and actual. So in the above example for January, I'm getting a variance of 28% + 17% = 45% rather than the 20% I want it to show. The variance % field is a calculated column in power pivot.
Is there any way for me to have the calculated field calculate on what is shown in the other columns rather than sum everything?