Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Hi Swuehl,
Thanks for the reply.. It worked for me.