I have a table that looks something like
<tbody>
</tbody>
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?
Person | Month | Target | Actual | Variance % |
A | Jan | 1075 | 1500 | 28% |
B | Jan | 4109 | 4950 | 17% |
A | Feb | 1084 | 4000 | 73% |
B | Feb | 1000 | 2250 | 56% |
<tbody>
</tbody>
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?