To show sub total as 100% in pivot table

[Anonymous]

Hi,

I want to show the values in my pivot table as 100% for each sub total

I need to show my pivot table like below

Cg1	Cg2	Expr1	Expr2
	Total	100%	100%
xxx	aaa	25%	50%
	bbb	25%	30%
	ccc	50%	20%
	Total	100%	100%
yyy	ddd	10%	60%
	eee	70%	40%
	fff	20%	-
	Total	100%	100%
zzz	ggg	15%	90%
	hhh	75%	10%
	iii	10%	-

Now I can show the partial total by doing the settings on Presentation tab.

My concern is I am getting the overall total as 100% but not the each sections total as 100%. The 100% should be shown for all fields in the cyclic groups.

So What should I do to get 100% value in each section??

Can anyone help me in this??

Thanks in Advance

Regards,

Leni

[#2]

Hi,

Can u post ur Expr1 & Expr2 ?

Regards

[Anonymous]

Sure.

Please find the expression below:

Expr1 = Sum(Amount)/Sum(Total Amount)

Expr2 = sum(Qty)/Sum(Total Qty)

Regards,

Leni

[#4]

Hi,

use this :

Expr1 = Sum(Amount)/Sum(Total<Dimension1> Amount)

Expr2 = sum(Qty)/Sum(Total<Dimension1> Qty)

Dimension1 shud be CG1 here

Hope it helps!!!

Regards

[Anonymous]

Try the Aggr() aggregation function:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {,dimension})

"Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides."

You could probably use something like:

    Aggr(Sum(Amount)/sum(TOTAL Amount),Cg1)Cg1cg1)Cg1)

See "Sum of Rows in Pivot Tables" section in Reference Manual (ppage 853 in v11.0 Manual).

[Anonymous]

Hi,

Thanks for the expressions.

But I am getting error when I use like sum(Amount)/sum(Total <cg1> amount)

I think we cannot use like SUM(Total <cg1> Amount) like this.

Thanks,

Leni

[#7]

Hi,

what are the dimensions of ur cyclic group & r u using the cyclic group directly in ur pivot table, if yes, then it can't show like it is showing in ur question.

Regards

[Anonymous]

My Dimensions are cg1 and cg2.

[#9]

r u using the cyclic group directly in ur pivot table ??

[Anonymous]

Yes.