Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Report Format - Pivot Table not working
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Report Format - Pivot Table not working
Melissa wrote:
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 Hi Melissa, I get the gist of the problem -- precalculated (aggregated) amounts in the source data can make it tricky to do accurate pivots. Would you provide a few rows of representative source data? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Report Format - Pivot Table not working
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. "smartin" wrote: Melissa wrote: 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 Hi Melissa, I get the gist of the problem -- precalculated (aggregated) amounts in the source data can make it tricky to do accurate pivots. Would you provide a few rows of representative source data? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Report Format - Pivot Table not working
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 LevelSubtotalsnone AdvancedSort orderAscending Double click on Last nameSubtotalsNone Double Click on LevelAdvancedSort orderAscending 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. -- Regards Roger Govier "Melissa" wrote in message ... 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. "smartin" wrote: Melissa wrote: 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 Hi Melissa, I get the gist of the problem -- precalculated (aggregated) amounts in the source data can make it tricky to do accurate pivots. Would you provide a few rows of representative source data? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Report Format - Pivot Table not working
Melissa wrote:
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. Hi Melissa, This is an interesting case because of the recursive nature of the logic. I have been working at reverse-engineering your data to get amounts per person and have something that works... I placed your sample data in A1:E6. In G1:I1 I placed values 2, 1, 0. These are your [Relative level] values, from [max - 1] to 0 (the order of these values does not matter). Place in G2 and fill through G2:I6 =$D2-SUMPRODUCT(--($A2=$C$2:$C$7),($D$2:$D$7),--($B$2:$B$7=1+G$1)) The result per person is this formula in H2 (fill down): =MIN(G2:I2) What this does... well... ask if you would like an explanation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"A pivot table report cannot overlap another pivot table report" | Excel Worksheet Functions | |||
how do I format data into columns using a pivot table report? | Excel Discussion (Misc queries) | |||
pivot table format report - setting default | Excel Discussion (Misc queries) | |||
A pivot table report cannot overlap another pivot table report. | Excel Discussion (Misc queries) | |||
Pivot table report | Excel Worksheet Functions |