Pivot Table problem

[user35]

I have a pivot table which is used for sales analysis. My data table has multiple lines associated with an order. My pivot table summarizes the lines (captures the main line and discounts associated with that). In my data section, the pivottable correctly calculates the NET USD AMOUNT, which is a sum of 3 line items (1 sales and 2 discount line items) off my data table. However if I want to get this amount in foreign currency, by dividing by the exchange rate (also a field in the date table), I get the sum of the NET USD AMOUNT/sum of the Exchange rate, where I really don't want the sum of the Exchange rate, just want it once. I created the above incorrect formula in Formulas/Calculated Field in Pivot Table menu.

Any ideas on how to handle this within Pivottable? I could change my program that creates the datafile, but am concerned about what problems that may cause.

 

[SkipVought]

Hi,

Is your source data on a sheet?

How is it acquired?

How about doing the calculation in the source table?



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Might have to.. Source is an Excel sheet generated by a crystal report... However this source sheet is tied to about 50 separate pivots.. I was hoping not to have to tamper with that for fear of breaking something....

 

[SkipVought]

Why? Adding a field won't hurt anything. Of course, save the original and work on a copy.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Also, person who uses the report may have manually adjusted the data tables to take out some records... So if I fix in the Crystal, would need to re-create data tables..

Just wondering if anyone had ideas for fixing at the Pivottable level.

 

[SkipVought]

I am not suggestion to fix it in Crystal.

Add the formula in the column adjacent and to the right of the table.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Sorry Skip,
Am slow this morning... Are you suggesting adding a formula in a column in my data table? Or the Pivot? If I add a formula in my data table, that will get over-written if I do execute the Crystal again to create the new data table...
Thanks!

 

[SkipVought]

"...will get over-written if I do execute the Crystal again ..."

How is it acquired?

What METHOD?



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

The crystal report creates the Excel spreadsheet which is the datafile for the PivotTable. Crystal creates a completely new data file upon it's execution

 

[SkipVought]

Oh, well all you have to do is query THAT workbook faq68-5829, and check the Data Range Properties - Fill down formulas in columns adjacent to data

Then each time you refresh the query to bring in new data, the formula will calculate thru the entire row range.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Lost me again...So I run an MS QUERY against my datafile.xls and somehow I manipulate the file. So I would run my Crystal Report, which creates the datafile.xls then run this MS Query against the datafile to prep my data. Then I would be able to open and refresh my Pivot Table (which is another spreadsheet)?

 

[SkipVought]

Sounds like a plan.

Or, your query could do what the crystal reports does, and bypass those steps.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]