Pivot table calculate field


A

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
 
Ad

Advertisements

H

houghi

Adam said:
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
 
D

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top