pivot tables and calculated

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Train Strikes - 7th, 12th Nov 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Tables and Calculated Fields

Pivot Tables and Calculated Fields

resolvedResolved · Urgent Priority · Version 2003

Paul has attended:
Project Intro Intermediate course
Excel Advanced course

Pivot Tables and Calculated Fields

Hi

I have three colums of data in an excel sheet

A TOTAL Revenue
B % of revenue apportioned to Business Line 1
C % of revenue apportioned to Business Line 2

I want to create a pivot table report that evaluates the total revenue due to BL1 and BL2 based on the data in Columns A,B and C. I have tried making a calculated field but that keeps coming up with the wrong vales, from what I can see on the internet that is because the formula A*B or A*C is applied to the SUM of columns A and B, not the individual entries. Is there a workaround please?

This is an urgent enquiry

regards

Paul Fisher

RE: Pivot Tables and Calculated Fields

Hi Paul

Thanks for your question

If my understanding is correct Columns B and C contain percentage figures showing what proportion of total revenue they take.

The simplest solution would be to add two extra columns to the source data and calculate the actual values there. You could then manipulate that data in a pivot table.

If this approach is not suitable, then please get back to us so we can try something different

Regards

Stephen

RE: Pivot Tables and Calculated Fields

Hi

This approach is not appropriate as the array of data involved is actually more complex than I described. Actually I have many business lines and assets supporting those business lines and I need to be able to report instantaneously, by dragging a field into the data array on the pivot table, what the revenue per business line is. I am also using a pivot chart to rapidly generate the charts that I need (revenue or margin by asset/business line/geographic region/period etc;) The pivot table / chart approach seesm to me to be the simplest way of achieving this.

regards

Paul

RE: Pivot Tables and Calculated Fields

Hi Paul,

Thank you for your question and sorry for the delay in responding.

I have attached a spreadsheet showing you what I have achieved.

Is this what your goal was?

Regards

Simon

Attached files...

Pivot example.xls


 

Excel articles

Please browse our web site to find out more about
excel-xp-training and other training courses.

Excel tip:

Showing all menu items

If you go into a pull down menu you usally find that you get a selection of items(this is the default) or sometimes everything. If you only get a selectiopn you have to go to the double arrows at the bottom of the menu and click it to get all of the hidden items.
To turn this off so that you always get everything, go to Tools / Customize / Options Tab / "Always show full menus". Make sure there is a tick in the box and you will always have every item.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.