Calculation within the same dimension

Post Reply
VV_VV
Posts: 6
Joined: Wed Oct 14, 2015 1:49 am
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Calculation within the same dimension

Post by VV_VV »

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?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation within the same dimension

Post by Wim Gielis »

What do you now have as a rule ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
aboyzhou
Posts: 15
Joined: Sun Jul 17, 2016 4:21 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Calculation within the same dimension

Post by aboyzhou »

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
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Calculation within the same dimension

Post by MarenC »

Hi,

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

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

Code: Select all

['Department 1', 'Department %']=N:

100 - ['Other Departments total', 'Department %'];
Maren
VV_VV
Posts: 6
Joined: Wed Oct 14, 2015 1:49 am
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: Calculation within the same dimension

Post by VV_VV »

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.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Calculation within the same dimension

Post by MarenC »

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
sven912
Posts: 5
Joined: Wed Mar 04, 2015 3:29 pm
OLAP Product: TM1 \ Planning Analytics
Version: PAL + PAW 2.xx TM110.2.xxx
Excel Version: 2016+

Re: Calculation within the same dimension

Post by sven912 »

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.
Post Reply