TM1 Forum

Hello,
Here is an issue. I have a department dimension that is used for data allocation. The dimension looks like this:
Total Departments
Department 1
Department 2
Department 3
Department 4
I need to be able to calculate the percentage in Department 1 as 100% minus all other departments. I can easily do it in the process, but I would like to have a rule. What would be the best way?

What do you now have as a rule ?

Hi ,
Mybe I guess that you would like to get this formular:
DP1 - OthDP = TOTDP - (TOTDP - DP1)*2

Is that right or not?
You can try it with TM1 rule.

Michael Qian

Hi,

This is so incredibly easy that I wonder if you have stated your requirement correctly!

Create a consolidation of all other departments (i.e. exclude Department 1) and then do as follows:
Maren

Maren,
It's incredibly easy when you have one hierarchy and one element that should be calculated like this. Now imagine the hierarchy with multiple consolidations, and your allocations can go to the elements under different parents, and your target element also changes based on the selection in the source dimension. Basically, the simplest version of the cube will look like this:
- Source Department
- Target Department
- Measure (Percent)
Now when the source is A, the target can be A, B, C, D..., when the source is B, the target can be B, A, D, E, N, ext. The calculation should apply to the target element that corresponds with the source.
I picked the easiest solution, I think, by creating an element outside of the entire hierarchy and applying a formula to it: 1 - Total Departments. Then I use it in the calculation cube when the source department = target department.

Hi,

In your original post there was only one element and one hierarchy. I added the rolling eyes in my reply because I suspected you had not given us the full story!

But it is good that you managed to find a solution.

Maren

Create a 2nd consolidation called "All Departments exc Dept1" with all departments except Dept1 rolling into it - then you should have the basis for want you need.