Have you already been stuck with a circular reference in TM1/Planning Analytics?
One of the main reasons why TM1/Planning Analytics has been so successful over the years is its calculation engine. TM1 can resolves very complex calculations over millions of cells in an instant. However, its only weakness is that it will need a little bit of help to resolve an equation with a circular reference. A circular reference is when there is a formula in a cell that directly or indirectly refers to its own cell:
In the equation above, to calculate the Dealer Margin value, the equation needs the value of the Dealer Margin:
Even though the equation is correct, you will have to resolve this circular reference first before being able to calculate the Dealer Margin value in TM1.
This article will explain you how to resolve this circular reference by using a bit of mathematics:
Circular Reference in TM1/Planning Analytics
TM1 does not do circular references, the following formulae in a TM1 rule will result with #N/A value:
['Dealer Margin'] = (['MSRP']-['VAT']-['Consumption Tax']) * ['DM%']; ['Consumption Tax'] = ['W/S Price'] * ['Consumption Tax %']; ['W/S Price'] = ['MSRP'] - ['Dealer Margin'] - ['Panda Fund'] ;
#N/A in TM1 could mean either there is a division by zero or there is a circular reference in the equation. To solve the circular reference, we will have to manipulate the equations.
Resolving Circular Reference
Our objective is to transform the circular reference system into a linear system:
Okay so let's do it!
We have a sytem with three equations with a circular reference:
How to resolve this system
- Simplify the system by removing the parentheses so it will be easier to manipulate the equations.
- Break the circular reference: We need to gather the 3 equations into one main equation.
Simplify the system
First to simplify the system we need first to get rid of the parentheses in the Dealer Margin equation:
Our new system is now as below:
Break the circular reference
Now we need to choose one equation and add the other two equations into this new main equation. You can choose any of the three equations to be the main one, in this example we choose the W/S Price equation:
To break the circular reference, we are going first to replace the Dealer Margin definition into the W/S Price and then we are going to do the same for Comsumption Tax into the Dealer Margin:
After a few steps, the W/S Price equation will not be linked to the Dealer Margin value.
Step 1: Substitute Dealer Margin to the W/S Price formulae
Step 2: Substitute the Consumption Tax with its definition to W/S Price
It's almost done, now we just need to simplify this main equation.
Step 3: Move to the left side W/S Price * Consumption Tax %*DM%
Step 4: Factor the W/S Price
Step 5: Divide both sides with (1 - Consumption Tax %*DM%)
That is it! Our equation is now:
If we now update our TM1 rule:
['Dealer Margin'] = (['MSRP']-['VAT']-['Consumption Tax']) * ['DM%']; ['Consumption Tax'] = ['W/S Price'] * ['Consumption Tax %']; ['W/S Price'] = (['MSRP'] - ['MSRP']*['DM%'] + ['VAT']*['DM%'] - ['Panda Fund']) / (1 - ['Consumption Tax %']*['DM%']) ;
Refresh the cube and the #N/A will have disappeared:
Remember this when kids say, “I’ll never use this math stuff in real life”…