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
Pivot example.xls
