PivotTables: Calculated Items

Airline Travel » PivotTables: Calculated Items

Airline Travel » PivotTables: Calculated Items — Fri, 09 May 2008 10:27:11 GMT

PingBack from http://www.travel-hilarity.com/airline_travel/?p=4425

re: PivotTables: Calculated Items

cbosleeds — Fri, 09 May 2008 10:58:48 GMT

I really like the forecasting equation - very slick. I might have a look at playing with something like this myself.

How did I not know that before?!

TechieBird — Fri, 09 May 2008 13:40:13 GMT

Thanks for a great post - I use Pivots all the time and thought I was a dab hand, so I'm scratching my head over how I didn't find this feature before!

You've now prompted me to have a proper dig around to see what else I'm missing...

re: PivotTables: Calculated Items

Chyrsostom — Fri, 09 May 2008 17:04:28 GMT

This is a nice feature, but it's performance seems slows against an external SQL 2005 table that has about 150k rows and 15 columns. It took about 5 minutes to update a budget variance calculation in this scenario. Without the calculated item, the performance is sub-second.

re: PivotTables: Calculated Items - correction

Ivan Trofimov — Mon, 12 May 2008 11:36:47 GMT

quote: "They accomplished this by adding a calculated item to the "Product Department" field that looked like this: =IF('Alcoholic Beverages'=0,NA(),'Alcoholic Beverages'/SUM('Alcoholic Beverages',Beverages,Dairy)). You can see this calculation uses a few Excel functions (IF, NA, SUM) in addition to referencing other items in the field in order to calculate the percentage in question. The reason for the IF and the NA functions is to return #NA if there are no alcoholic sales instead of a divide by 0 error."

this does not make sense, couse the #DIV/0! error will appear only if the consequent of a dovision is null, i.e. sum of all classes. Value for true event as described in formula will be zero by itself.

ps the other stuff is cute

re: PivotTables: Calculated Items

John — Mon, 12 May 2008 16:59:55 GMT

Using this same concept would it be possible to add Sparklines to a Pivot Table with the REPT() function?

re: PivotTables: Calculated Items

Pat — Wed, 14 May 2008 01:39:05 GMT

This is great. Does this function work with cube (SQL) based pivot tables? Adding new calculations only seems to be manual.

re: PivotTables: Calculated Items

sam — Fri, 16 May 2008 14:46:22 GMT

David ...this is nothing new....Its been there right from Excel 97....so thats for about 10 years...probably before your times

We were hoping to see some improvements to this feature in 2007.. for example

a) Ability to have both calculated field and Calculated Items in the same pivot....

b) Ability to refer to a field in a formula in a caluclated field or item

Example if('Product Family' = "BC", 10%*Sales,0)

c)Ability to use names and array formulas in a caulated filed or item...

Sam

re: PivotTables: Calculated Items

David Gainer — Tue, 20 May 2008 01:49:12 GMT

Chyrsostom - thanks, I will take a look.

John - unfortunately no, since this feature cannot support references in the calculation

Pat - unfortunately, no. That's an area we are thinking about.

Sam - you would be suprised at how old I am :-)

You are correct that this feature is not new, but it is one of those features that is not well known, so I figured a little evangelism couldn't hurt. Your feedback is on-target, and we appreciate it.

re: PivotTables: Calculated Items

Colin Banfield — Tue, 20 May 2008 23:14:18 GMT

Dave, it's good to see you back here after your sabbatical :-) I agree that the article could benefit the many folks who aren't familiar with these PivotTable tools. For the rest of us, calculated measures (items & fields) reminds me of a sensitive tooth - you get so used to workarounds that eventually you ignore the problem (in this case, the limitations).

This is changing the subject, but in terms of PivotTable features with limitations, nothing comes close to PivotCharts. In fact, I'll go out on a limb to say that if PivotCharts were able to properly represent multidimensional PivotTable data visually, it would be one of the most powerful feature in all of Excel.

Microsoft Excel : PivotTables: Calculated Items

Dating — Sat, 31 May 2008 18:58:21 GMT

Today's author: David Gainer, a Program Manager on the Excel team. PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so

Microsoft Excel : PivotTables: Calculated Items

Weddings — Thu, 05 Jun 2008 13:19:26 GMT