Pivot Table % from subtotal

[neveu]

hello forum,

would need to know if this is possible in a pivot table in XL 2007.


i have a table like this
Process1 Process2
country subprocess1 subprocess2 subprocess 3 subproces1 subprocess 2 ..
country1 1 2 3 2 4
country2 3 4 5 5 6
country3 3 5 5 3 4


i would like to see the % for each subtotal like this:
for example for process1 to calculate the percentage of each value in the subtotal ( 1/(1+2+3) ; 2/(1+2+3) ; 3/(1+2+3)

Process1 Process2
country subprocess1 subprocess2 subprocess 3 subproces1 subprocess 2 ..
country1 16.67% 33% 50% 33% 66%
country2 3 4 5 5 6
country3 3 5 5 3 4

and the same for the other lines..

do you know any solution to this?

thank you

 

[cyrilbrd]

Hi,
like this?
Assuming that I change your headers to something shorter (sbp1 in lieu of subprocess1...)

country	sbp1	sbp2	sbp3	sbps1	sbps2
first	1	2	3	2	4
second	3	4	5	5	6
third	3	5	5	3	4

The pivot would give:

	Values				
Country	Sum of Field1	Sum of Field2	Sum of Field3	Sum of Field4	Sum of Field5
first	16.67%	33.33%	50.00%	33.3%	66.7%
second	25.00%	33.33%	41.67%	45.5%	54.5%
third	23.08%	38.46%	38.46%	42.9%	57.1%
Grand Total	22.58%	35.48%	41.94%	41.7%	58.3%

if yes:
1-create your pivot table.
2-Place country in Row labels
3-Go to the ribbon click on Fields/Formula/Calculated Fields
4-Insert Field
and there you type exactly what you want, so your

1/(1+2+3),2/(1+2+3),3/(1+2+3)

becomes:

=sbp1/(sbp1+sbp2+sbp3)

click ok, and continue with item 4 till you completed all the desired formulas.

=sbp2/(sbp1+sbp2+sbp3)

=sbp3/(sbp1+sbp2+sbp3)

each with it's own filed name...

please advise if this is what you are referring to.

 

[neveu]

hi Cyril,

your solution is very innovative and it workd.

however, in my case, on top of each subprocess there is a main process.

will try again to see if i manage to make it work with a pivot table

 

[cyrilbrd]

Ok, keep us posted.

 

[neveu]

hi I've found a more ellegant solution.

i've moved the field from the column that generated subtotals to the rows.
i've got the data that i needed by calculating the values as percentage from row

hope it helps the others

 

[cyrilbrd]

cool.