Calculate Column % in Pivot Table

[deekey2]

Hi All,

I have a table as below:

Category	Type	QTY
A	D	10
A	E	12
B	F	20
B	D	30
C	E	40
C	F	50
A	D	60
A	E	120
B	F	90
B	D	50
C	E	90
C	F	200

And I made a pivot table (X:Type / Y:Category) , which sum the QTY like this:

	D	E	F
A	70	132
B	80		110
C		130	250

Then I would like to convert as %  by each column.

	D	E	F
A	47%	50%	0%
B	53%	0%	31%
C	0%	50%	69%

I tried to use: sum(QTY) / sum(total QTY)

but the part "sum(total QTY)" returns 772 (total of all QTY)

I tried and worked for hard code like:

sum(TOTAL if(Type='D', QTY)),

but if I get dynamic like:

sum(TOTAL if(Type=subfield(type,',',1), QTY))

it returns 772 again..

Any ideas? thanks

[MayilVahanan · Accepted solution]

Hi 

Try like below

sum(QTY) / sum(total<Type> QTY)