Pivot Table - Calculated Fields

[dohsan]

Hi everyone,


Have a question about calculated fields to pick your brains about.


The current data source for our pivot table analysis is an OLAP cube and it appears that you cannot add in calculated fields when using this as a source.


Is this the case of am I missing something?


The current options seem to be only:

a) added the fields to cube where the aggregations can take place in the source data itself

b) add in standard excel forumlas next to the pivot table to do the calculations.


The issue with a) is we may not be able to add them in and b) doing the calculations this way isn't dynamic if the table is changed (data could be overwritten)


Is there any way around this or are these the only 2 options?


Cheers


Dohsan

 

[dohsan]

Just to add, the reason I was asking was I saw Chandoo's post today and was wondering if it was possible to do similar, but comparing measures to different measures, rather than comparing the same measure.

 

[Trader273]

I always found adding calculated fields in pivot tables to be somewhat restrictive. Only makes sense though, since the data is coming from only one source. A couple of ideas:


1) Connect your data to PowerPivot add-in. You can do much more with pivot tables that way.


2) Use your cube data ( there are some powerful cube functions in excel) and create a report that way, without a pivot table. Then you can put whatever you want, where you want. The catch is just have to create some sort data filtering that is tied to your cube formulas and then any subsequent formulas. Could use drop down menus.


3) Create a pivot table, and then use the GetPivotData() function. Create your pivot table as you normallly would, and somewhere else just enter ' = and point to a cell in the pivot table. It will produce a weird ( and sometimes intimidating) formula. (if you dont see a formula, select inside your pivot table--Pivot Table Options--Far left Options drop down--Generate Pivot data. Excel does a weird thing and hard codes the cells, so just have to mess around with that. That way you can have your pivot table data ( which will be filtered anyway you want) as well as your measures that you want to create


Guess it comes down to what you want to accomplish, what you already know, and how much time you want to put into it. Hope this helps and not confuse you. =)