Ways to speed up pivot tables with many calculated items?

[cschmitt11112222]

Hi all, I have built a P&L constructed in a form of pivot table with a # of calculated items built into it generating financial metrics like net revenue, asp, unit cost, etc. There are about 10 of these calculated items working in the pivot w/ about 20K lines of raw data.

When dragging the fields around in the pivot – usually 2 — it takes about 3-5 min to complete refreshing (I think because it seems to calculate against every piece of data, not just the fields I am filtered on or need). Adding a 3rd field can take 20 + mins. Is there a way I can speed up the time is takes the pivot table to return a new view of the data? Turning off option DEFER LAYOUT UPDATE in table options doesn't work here….when I make the update with it off it still takes the same, large amount of time. If I remove the calculated items the speed problem goes away, so I am quite certain that is the root cause of the issue…....Building these metrics into the raw source data is not a good option for what we are using the pivot table for so that is out as a solution. Does anyone have other ideas on this one? Cant seem to find a good solution, and been working on it for a few months now

Thanks in advance

 

[NARAYANK991]

Hi Casey ,

Since you have earlier taken up your problem on Mike Alexander's site ( http://datapigtechnologies.com/blog/index.php/speeding-up-pivottable-updates/ ) , I don't think you can get better advice in another forum.

In case you haven't checked this link out already , see if the suggestions to shift to PowerPivot can be implemented in your case.

http://excelusergroup.org/forums/p/2096/5903.aspx

Narayan

 

[cschmitt11112222]

Thanks for the reply, Narayan - good to know on the other site. Can always hope someone else might have a unique solution though...although I am beginning to think one doesn't exist

We did try powerpivot...it did make it faster, but unfortunately the user experience is not as good as regular excel pivot tables- for example, you cannot build in calculated items into power pivot on their own (you can group calculated fields together to create a single P&L for example, but you cannot then filter down into the different P&L components -- essentially everything has to be built as a calculated field instead of item) and we also had some issues with formatting that do not exist in regular excel pivot tables.