Percent total not correct

[#1]

Hello All,

I am on version 8.5. I can't get this calculation to work correctly in a pivot table.

Sum(amount)/Sum(ALL amount) I also tried replacing ALL with TOTAL.

What i want is somthing that looks like this

Class	DateCycle	09-28-2009	09-21-2009	09-14-2009	09-08-2009
Cons		30%	32%	29%	30%
Other		70%	68%	71%	70%
Total		100%	100%	100%	100%

What it is doing is ignoring the Class and Date breakdown and giving the percent of the full total.

Class	DateCycle	09-28-2009	09-21-2009	09-14-2009	09-08-2009
Cons		2.63%	2.52%	2.76%	2.58%
Other		26.89%	23.44%	21.11%	18.07%
Total		29.52%	25.96%	23.87%	20.65%	100.00%

The Class is an if statement if(class = 'CONS', 'Cons', 'Other')

The strange thing about it is that I took a training class last week and made the same calculation and it worked, the only difference is the class was with version 9

Thanks in Advance for any help.

Tom

[Oleg_Troyansky]

try this:

sum(amount)/sum(total <DateCycle> amount)

[#3]

Oleg,

Thanks! Worked perfect.

Tom

[#4]

I spoke to soon. It works when the roll up is by month. When it is by week, it does not.

By Week Day
CLASS	DateCycle	09-28-2009	09-21-2009	09-14-2009	09-08-2009
Cons		2.63%	2.52%	2.76%	2.58%
Other		26.89%	23.44%	21.11%	18.07%
Total		29.52%	25.96%	23.87%	20.65%
By Month
CLASS	DateCycle	Oct	Sep
Cons		8.92%	11.15%
Other		91.08%	88.85%
Total		1	1

[Oleg_Troyansky]

the field name after the keyword "TOTAL" should correspond to your dimension - wither a date, or a month or a week. If you use a dimension Group, try using Group name after the "TOTAL", it might work (I haven't tested it). If it doesn't work, you might have to give up on using a group.