March 20, 2014 at 10:31 am
I'm trying to figure out the proper MDX for a calculated member so that it only does the calculation on members of a specific hierarchy when it is a certain value. I thought I had it, but I am consistently getting the dreaded 1.#INF (divide by zero) even though I thought I trapped it properly in the beginning:
WITH MEMBER [Measures].[MyNewCalculatedMember] as
IIF([Measures].[ExistingMeasure]=0, null ,
SUM( EXISTING ([Dimension].[HierarchyRoot].[HierarchyLeaf].MEMBERS,
IIF([Dimension].[HierarchyRoot].CURRENTMEMBER is [Dimension].[HierarchyRoot].[HierarchyLeaf].&[2],
[Measures].[OtherMeasure] / [Measures].[ExistingMeasure], null) ,
IIF([Dimension].[HierarchyRoot].CURRENTMEMBER.PARENT is [Dimension].[HierarchyRoot].[HierarchyLeaf].&[2],
[Measures].[OtherMeasure] / [Measures].[ExistingMeasure], null)))* 1000)
I'm pretty sure I'm totally hosing this up as I'm a complete MDX noobie, but this seems to be close. If I filter down to the leaf level, I get what I expect with values or nulls if the denominator is null.
When I roll it up to the parent of the hierarchy, I get the dreaded 1.#INF.
Isn't there an easier way to do this with something like Ancestor or something?
Any help would be greatly appreciated!
March 20, 2014 at 11:45 am
You'll probably want to use a combination of the SCOPE assignment operator together with the LEAVES function and "THIS" statement instead of using the IF function and testing the coordinate's position.
Calculated members do NOT influence the base value of a parent member, but cell calculations using the SCOPE assignment operator can cascade upwards. 🙂
Also, SCOPE statements are usually better performing than IFs. Hope this helps.
EDIT: You must also check for the empty condition using ISEMPTY() to avoid division by zero. Check out this blog--more specifically the PercentOfParent calculation:
http://sqldusty.wordpress.com/2012/02/28/ten-mdx-calculations-for-your-cube-part-2-2/
March 20, 2014 at 12:10 pm
Thanks for the quick response. I'll start looking into those right now and hopefully be able to fix my errors 🙂
March 20, 2014 at 12:27 pm
You caught me on a good day 😉
March 20, 2014 at 12:44 pm
Yeah, this will take a while...seems like it might be overly cumbersome in terms of code as we are trying to keep our calculated members pretty concise as we have quite a few.
Just looking at the blog sample, I'm not entirely sure how I can convert that over to a calculation that is taking two measures, the example is doing a calculation based off of a measure and the dimension?
March 20, 2014 at 1:26 pm
In business terms, what are you trying to do? What is the rationale driving the calculation?
March 20, 2014 at 1:34 pm
We are essentially trying to calculate how much revenue is generated per unit filtering it down to a specific branch of a product. Seems like this should be really simple, but ugh. Not really knowing MDX makes it a little difficult 🙂
I tried using something along the lines of IIF(IsLeaf([Dimension].[TopHierarchy].CurrentMember),<do calculation>),sum([Dimension].[TopHierarchy].CurrentMember.Children,[Measures].[MyNewMeasure].
That almost works, but as soon as I try using either Filter or Existing, I get #error :/
March 20, 2014 at 1:41 pm
Without seeing the data model, I am going to say try your original solution, but make sure you test for the member being empty as WELL AS being equal to zero.
Let's see if that works.
I am trying to give you a quick solution without having you rewrite the solution.
March 20, 2014 at 1:43 pm
Thanks...I have it almost figured out, but what's odd is putting in my filter for the value is what's throwing it off now. The IsLeaf is working, it's calculating, but with either Filter or Existing in there it's throwing back errors. I'm wondering if it's because I'm just checking if it's = 0....ugh. I'll try that!
--- EDIT ---
This did not resolve the issue. My guess is that it's a fundamental misunderstanding that I have with how this should be working. I don't think what we're looking for is that difficult, but for the life of me I cannot get this to work. We already have calculated measures that work, unfortunately none of them filter on a specific dimension member to only calculate values for that.
March 21, 2014 at 12:54 pm
Did you get this resolved? I wish I could help you more, but I am really having trouble envisioning the solution. Forgetting the MDX for just a second, what would your pseudocode be?
You may be trying to do too much at once.
EDIT: Also, by just using IIF and NOT using SCOPE, your query performance will, more than likely, suffer. Check out Chris Webb's approach in this blog:
https://cwebbbi.wordpress.com/category/mdx/page/2/
I am hoping this triggers an idea for you.
March 21, 2014 at 10:10 pm
Yeah, not quite there yet. A large part of it is that I am pretty new to MDX.
Pseudo code would be:
for every member of product
divide x by y
only if product.category = 2
then roll this up to an aggregate
The aggregate needs to be able to be sliced from the top of the dimension down to the different hierarchies in the dimension.
I know it's not much, but that's the best way I can describe it 🙁
March 24, 2014 at 7:54 am
RE: sliced from the top of the dimension down to the different hierarchies
Do you mean "only roll up (x/y) from the leaf level of product if its ancestor is Product Category 2"?
March 24, 2014 at 7:59 am
Haha yeah, sorry about that...ugh. The pseudo is not strong in this one.
March 24, 2014 at 8:07 am
NP.....
Does this calculated measure, after applying the division, rollup up along all other "non-product dimensions" in your cube by summing (x/y)?
If you don't hear from me the rest of the day, it is because my wife is going into labor with our first child 🙂
March 25, 2014 at 9:19 am
Hmm I don't think so. The division is there to properly calculate our forecast actuals and needs to be applied across the board. I had attempted to do this by doing that calculation at the leaf level, and then doing the sum at the top to aggregate it.
The filter portion is so that we are only performing this calculation when it is of a specific type.
And congratulations! I hope all is well 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply