Excel - Pivot Table Calculated Item

[chouna]

Can anybody show me how to create a Calculated Item in a pivot table? I have a field called CLASS with the following values: Stock, Sales, Order and Target. I would want a field that calculates Stock minus Order. As per HELP, we have to ungroup each item and enter the calculation. I have done that but have never been successful in creating one. Either the system hangs up or I get a message that Calculated Item should be of the same field, which was how I defined it.

Am I the only one who is having problem with this?

 

[SilentAiche]

[blue]chouna[/blue]

This doesn't directly answer your question (apologies), but it may offer a solution. Would it be feasible to do your calculations outside the PivotTable? I frequently add columns directly to the right of my PivotTables and perform calculations on the figures within the table. It works well, except that when the Pivot area grows (i.e., new rows added) I have to manually adjust my calculation columns to keep up with the growth. I'm sure this could be avoided with better planning on my part. Simple solution, but it often works.

If this helps at all, I'm glad.

Tim

 

[SkipVought]

Hi,

Please post a representative sample of source data for your pivottable.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[chouna]

Tim, that is how I am doing it right now but the users are very much against having to manually add a column and typing in the formula (you can't use a simple point-and-shoot in pivot tables unless you are willing to change all absolute references).

Skip, here is a sample data. Order/Sales/Stock/Trget columns are actually values in a single field called CLASS in the external source data (I don't own the data). I need to tack on another column that calculates Stock less Order. If the situation were Stock plus Order, I could have simply Grouped Stock and Order and Hide Details. But this is Stock minus Order.


product Order Sales Stock Trget
0100031 48 300 98 736
0100093 32 673 2,486 1,001
0100143 746 662 234 4,675
0101001 86 583 493 1,053
0101093 78 1,152 3,509 1,500
0101143 495 1,032 175 5,998
0102001 48 255 378 474

Thank you for your attention.

 

[SkipVought]

I went in and added a Calculated FIELD (not an ITEM) and just made the formula
[tt]
=Stock - Order
[/tt]
Seemed to work just fine
[tt]
Sum of Field1
product Total
100031 50
100093 2454
100143 -512
101001 407
101093 3431
101143 -320
102001 330
Grand Total 5840
[/tt]

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[chouna]

Hi Skip,

Problem here is that Order / Sales / Stock / Target are not fields in the source data. They actually fall in one field called CLASS. The source data looks like this:

ITEM CLASS QTY
0100031 Order 48
0100031 Sales 300
0100031 Stock 98
0100031 Target 736

What I sent earlier was the result in the pivot table after cross-tabbing it. Sorry if I wasn't clear on that. I tried doing a Calculted Item on a very small data and finally was successful. But with a file of 50 records, the calculation took almost 5 minutes and gave me repeating and unnecessary records albeit the right calculation. I guess what I concluded as Excel giving up on me was actually Excel doing a background processing which was taking forever. I'm ready to throw in the towel on this.

 

[SkipVought]

Well thanks alot for posting something that was NOT source data when I asked for SOURCE DATA! I specifically asked for "source data for your pivot table" You've wasted my time AND yours.

I did a Calculated Item formula and it seemed to work fine. I don't understand your problem.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SilentAiche]

[blue]chouna[/b]

If the situation were Stock plus Order, I could have simply Grouped Stock and Order and Hide Details.

Could you convert Order to a negative number and then proceed as suggested in your quote?

Tim

 

[SkipVought]

FYI,

My PT has CLASS as column and ITEM as row

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}