Pivot Chart- Calculation time when using Variables to hide Dimensions
Hi all. I have a Pivot Chart where the dimensions are calculated depending on whats in a variable e.g. 'when Variable1='1' then Dimension1, when Variable1='2' then Dimension2' etc etc. This works correctly.
My issue is with the calculation time. If the user changes the variable from say 1 to 2 it takes quiet along time for the table to refresh. It currently says in the Sheet properties that the calculation time for the chart is '9641'. Which i presume is a lot?
Before i added the calculated dimension it worked perfectly. Whats confusing me even more is that when i actually just move the Pivot Chart from one position on the sheet to another (a simple drag) the table refreshes / reloads.
Is there some setting i can turn off to stop this refresh constantly happening? Or make the Pivot Chart more efficient?