Pivot - Calculated field in row area

[Milind Keer]

Hi All

I have following source data

Country........... Polulation
UK----------------100
USA---------------200
INDIA--------------300

Now can I create a pivot view which will give me Only UK population and
Other population? I don't want to display all other countries, it should just
sum up other contries and give me result as others.

Output in view

Country-------Population
UK-------------100
Other----------500

I tried Calculated fields?? but no luck...

Thanks in advance...

 

[Gilbert]

Hi Milind,

What I usually do in those cases, I add an additional column to my data
source. You might want use the if formula so that everytime you have UK it
will leave UK but if it's something else then have the formula return other.
Hope that helps.

 

[Milind Keer]

Hi Gilbert

Thanks for the prompt reply.

Ya!! I did the same thing, but I don't want to do that because,
1. My data source is MS Access Tables and not Excel
2. Now I have to export data from MS Access to Excel then need to add one
more fields and calculate Others
3. Currently thre are 45K records and in next few months we are expecting
more than 65K records and excel will not support those many records
4. I can write a query in MS Access to create one more field, but i don;t
know how to do that? Can help me on that,

How to write a query in MS Access to add one more customize field ?

thanks!
Milind

 

[Debra Dalgleish]

In the pivot table, you could manually group the countries, with UK in
one group and all other countries in the other.