Pivot table calculate field

[Adam]

Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

Many Thanks

 

[houghi]

Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

You need to reorganize your data
Retailer | TotalHr| RetailHr
004 | 5000 | 2500
009 | 6000 | 3000


If Total hours and Retail hours is all you have got, you can do an
autofilter and use that to first get all of the first (with the
retailers) then the second (with the retailers) and paste them next to
each other:

Retailer | value | Retailer|Value
004 | 5000 | 004 |2500
009 | 6000 | 009 |3000

Chech if the retails are the same on each line
Remove the second column retailer and rename the other columns

If this is possible will depend on how the rest of you data is put
together (and how much it is).

You could use conditional formatting to see if the first is the same as
the second. That should help you determine extremely fast if there are
differences or not on a glance.

houghi

 

[Dave Peterson]

When you create your pivottable, are you dragging the Retailer number to the row
field and the desc to the column field and the Value to the data field?

Then can you add the calculated field?

Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

Many Thanks

 

[Herbert Seidenberg]

Here is Dave's excellent suggestion
in pictures:
http://www.freefilehosting.net/download/3d59a