|
|
|
Rank: Newbie
Groups:
Joined: 8/8/2012
Posts: 2
|
I need to apply a different calculation formula depending on the result of a conditional statement based on a dimension value not on a measure value. See Example below
Dim M1 C1
A 80 M1 *.7
B 70 M1 *.6
C 85 M1 *.6
D 60 M1 *.7
E 80 M1 *.6
This needs to be reliable even with hierarchies. In the example above my basic C1 formula changes for A and D so it should go along these lines IF Dim="A" or Dim="D" THEN M1*.7 ELSE M1*.6
I have used MDX in the past to solve this and I have also used workarounds in Targit of this sort "if sum(d1, @"[A]", m1) = sum(d1, 0, m1) then" or evaluating the count " if count(d1,d1:0,m1)=1 or 4 in this case" and many other combinations (i.e. using levels for hierarchies, count comparisons, etc...)but I would like to know if something more independent of the measure is available because counts can change and measures can have the same values which will make my workarounds useless. I am sure there is and I just have not found it yet.
Thanks for any help
Antonio
|
|
|
Rank: Administration  Groups: Joined: 4/7/2011 Posts: 111
|
Hi Antonio, My approach will still be using a count function, like "if allcount(d1, d1:0, m1) = allcount(d1, d1:@"[A]", m1) then ...". See the screenshot example where I have used a syntax that will work on the top level of the hierarchy, no matter how the deep the hierarchy might be. Best regards, Ole I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S. Ole Dyring
|
|
|
|
Rank: Newbie
Groups:
Joined: 8/8/2012
Posts: 2
|
Thanks Ole for the answer however that solution made me run into another issue. When i use the formula you recommended I am no longer able to calculate intersections with other calculations i may have on the sheet and that is really frustrating. any help would be appreciated.
Antonio
|
|
|
Rank: Administration Groups: Joined: 4/7/2011 Posts: 111
|
That is true. The count function will be able to count dimension members only - and since you have no dimension members in e.g. a calculated Average row, it won't work. However, you *will* be able to do an intersection calculation the other way around, e.g. an Average on your count calculation. Not sure if this will be helpful to you. I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S. Ole Dyring
|
|
|
|
- Anonymous
|
