I had previously written a very technically focused description of what values you can expect when two calculations intersect in a Voyager workspace (see the above URL). I have since come up with a much less technical explanation comparing Voyager to what you can do with Excel formulas.
Imagine you have some data in Excel:
You now want to find percentage growth between 2008 and 2009 so you add a formula for each cell in column D.
Eventually giving
Now you are interested in just the total of B and C so you create an extra row and start filling in the formulas.
When you get to cell D6 which formula do you write? Is it the sum of D3 and D4 or the percentage difference of B6 and C6? It is clearly more useful to know the percentage growth of the 2008 and 2009 totals for A and B than it is to sum up the percentages in column D. So you can choose to enter that formula in cell D6.
However an OLAP tool like Voyager does not allow you specify formulas for individual cells. It only allows you to specify formulas for entire rows or entire columns. So the entire column D is specified as having the percentage difference formula and the entire row 6 has the ‘sum of A and B' formula.
This leads to a problem when it comes to cell D6 as there is a choice of two formulas: either the percentage difference formula or the sum formula. The tie is broken by examining a property of the calculation called the solve order. The calculation with the highest value for the solve order property wins. So to get the correct answer in this situation we need the percentage difference calculation to have a higher solve order than the sum calculation.
Unfortunately Voyager does not give the end user the ability to set the solve orders on calculations and all calculations are created with a predefined solve order. In the case of calculations that you create through the Voyager calculations dialogue all calculations are created with a solve order of 1.
So if both calculations have a solve order of 1 which one wins? This unfortunately does not have a clear explanation so it could come out with the right or wrong answer depending on how lucky you are.
The exception to this is the Voyager rollup calculation. This has special behavior as it has a lower solve order than any calculated member it interacts with (the roll up calculations uses the MDX AGGREGATE() function which has this special behavior). If instead of doing the sum of A and B in the above example, you do the rollup of A and B then you can guarantee that the percentage calculation will win.
A deeper more technically focused explanation of this can be found here Why are my calculations wrong?
