Report Format - Pivot Table not working

[Melissa]

I have this report of raw data, I would love to put the data into a pivot
table as its the best way to present. Problem is that the source data
precalulates the higher levels of the hiearachy in the raw data so in a pivot
table

Ex: Director, team manager and team employees give combined $1000
---------Team mananager gives combined $700 between self and team
-------------Team members give $500 total

So instead of drilling down
Director 1,000
Manager 700
team 500

I get
Director 2200
Manager 1200
team 500

Been trying to manipulate the raw data this way and that to get it to work,
any thoughts on how to get the data into pivot without all the new
calculations? Or any other format thoughts?

I just really like the drill down option, I have 5000 employees to show
detail on

 

[Melissa]

Here is few lines of data

Last Name Relative level Sup last name Credit issued
Center
Smith 0 Penn
1,000,000 WA
Jones 1 Smith
500,000 CHA
Oswald 2 Jones
200,000 CHA
Clark 2 Jones
200,000 CHA
Cortes 3 Clark
10,000 CHA

So for Smith a total of $1 million was given as credit, total of his credits
plus his direct report Jones. Jones gave $500,000 between credits issued by
him and his direct reports Oswald and Clark, and so on. This is just a sample
of the data (trying not to give to much away online since it is
confidential). I have been trying to manipulate the raw data to assist with
the pivot table but just cant get it to go, I tried breaking down each person
by the center they report to, re-organizing the hiearchy piece.

 

[Roger Govier]

Hi Melissa

Does it help if you insert another column in your source data just titled
"Level"
Copy the values from Relative level to this column.

In the PT
drag Relative Level to the row area, followed by last Name
drag Level to the Column Area
drag Credit to the Data area

Double click on Relative Level>Subtotals>none Advanced>Sort order>Ascending
Double click on Last name>Subtotals>None
Double Click on Level>Advanced>Sort order>Ascending

Double click on each item in Relative level to Group all the data to a
single value for that level
You will now get a cascading effect of the Totals at each Level

If you double click on any Relative level, you will get a drill down showing
the contribution of each person to that level.

If that doesn't provide sufficient for you, then introduce 4 more columns to
your source table, title Level0, Level1, Level2, and level3
In the Level0 column, copy a 0 down the whole of the range.
In row 2 of Level 1 enter the formula
=IF($B2>=column(A1), column(a1),"")
Copy across through the columns containing Level2 and Level3
Copy all three formulae down through the extent of your data

Drag each of these new Levels to the page area of your PT.
Now you can select to see Just the Level0's or Just the Level 1's etc, by
leaving the other page fields set to All, but selecting the Level required
for any one of the others.

I hope this helps.