Subtotal is not same as the summation of individual rows of Pivot table

[#1]

Hi,

In my calculation I have used the formula as IBPrevYrAdj = (1+((PartA Ratio/Part B Ratio) - 1)) * Filtered BO.

Here, the total at USA level in "IBPrevYrAdj" column is not same as the summation of all individual countries of USA i.e. sum of IS EAST+ IS Midwest... and so on after applying the formula.

Is there any way, I can have the subtotal of USA same as the summation of all individual countries in column IBPrevYrAdj.

Cluster	CountryL2	Country	IBPrevYrAdj	Adj Ratio	Filtered BO	Part A Ratio	Part B Ratio
AMERICAS	USA	Total	2,124,106.69	1.05	2,028,790.97	49,250,247.00	47,040,225.00
AMERICAS	USA	IS EAST	470,125.21	1.01	466,662.97	8,784,427.00	8,719,734.00
AMERICAS	USA	IS MIDWEST	439,876.37	1.02	431,956.43	9,665,635.00	9,491,606.00
AMERICAS	USA	IS SOUTH	569,848.49	1.05	542,449.04	11,062,249.00	10,530,354.00
AMERICAS	USA	IS WEST	421,749.43	1.03	408,879.00	8,973,982.00	8,700,125.00
AMERICAS	USA	MEXICO	106,197.60	1.04	102,444.25	1,694,475.00	1,634,587.00
AMERICAS	USA	OTHERS	100,171.60	1.31	76,399.29	1,195,626.00	911,885.00
AMERICAS	USA	PCMS USA	0.00	0.85	-	2,081,459.00	2,449,643.00
AMERICAS	USA	ULTRASOUND FIELD SERVICE	0.00	1.26	-	5,792,394.00	4,602,291.00
IBPrevYrAdj = (1+((PartA Ratio/Part B Ratio) - 1)) * Filtered BO

[swuehl]

In the pivot table, the total will be calculated as expression total, like evaluating the expression in a text box.

If you want sum-of-rows in the pivot total, you need to use advanced aggregation (search for sum-of-rows in pivot tables in the HELP), something like

=Sum( Aggr( YourIBPrevYrAdjExpressionComesHere, Cluster, CountryL2, Country))

edit: And you can't reference other expression labels like "Part A Ratio" within the aggr() expression, you need to write the full code.

[#3 · Accepted solution]

Hi Swuehl,

Thanks for the reply.. It worked for me.