Re-use of (Generic) Calculated member

Question

• Hi,

Is it possible to reuse a calculated member but then the set is based on another hierarchy but from the same dimension?

This is what i currently have:

CREATE MEMBER CurrentCube.Measures.AverageFlightCycle AS

SUM( [Component].[Manufacturer - Component].CurrentMember.Parent,[Measures].[Flight Cycles])
/
COUNT(NONEMPTY([Component].[Manufacturer - Component].CurrentMember.Parent.children)),
NON_EMPTY_BEHAVIOR = [Measures].[Flight Cycles],
FORMAT_STRING = "#,#",
VISIBLE = 1;

The hierarchy is [Manufacturer - Component] suppose i have a second hierarchy for example [Customer - Component] is there a way to write the mdx statement in such a way that this is more generic?.

rgrds,

Paul.

Thursday, January 07, 2010 2:28 PM

• OK, OK, maybe I was asking to much. Sorry, I'll try to keep this as simple as possible.

Let me see if I got it right.

You have a hierarchy on rows which you can expand (drilldown) to lower levels. And you want a calculation that works fine for one hierarchy as well as for another hierarchy of that same dimension.

What confuses me are your "parent" and "child" terms.

From what I've concluded, there are only currentmember and his siblings.

When you say sum of parent, that should be understood as the sum of currentmember's siblings. When you say children, my guess is that you automatically position yourself on that parent and you're looking at his children. But, in reality, the coordinate is still on currentmember (he is the one who's parent we just referred to) and therefore those children should be understood as his siblings. Only this time we're taking their count. Sum over count is an average. Average among siblings would be the proper terminology in this case if I'm not mistaken. So, that's what we're after!

Now, let's do some coding ...

Is this it?

 Column Labels CY 2003 CY 2004 Total Original Measure Total Average Measure Row Labels Original Measure Average Measure Original Measure Average Measure Europe \$9.015.795,82 \$13.997.909,91 \$6.748.193,39 \$8.602.987,45 \$15.763.989,21 \$22.600.897,35 France \$3.400.129,00 \$3.005.265,27 \$2.298.789,76 \$2.249.397,80 \$5.698.918,77 \$5.254.663,07 Germany \$2.157.272,42 \$3.005.265,27 \$1.962.012,12 \$2.249.397,80 \$4.119.284,54 \$5.254.663,07 United Kingdom \$3.458.394,40 \$3.005.265,27 \$2.487.391,50 \$2.249.397,80 \$5.945.785,90 \$5.254.663,07 North America \$29.096.718,73 \$13.997.909,91 \$15.749.980,25 \$8.602.987,45 \$44.846.698,97 \$22.600.897,35 Canada \$6.187.089,89 \$14.548.359,36 \$3.063.889,72 \$7.874.990,12 \$9.250.979,61 \$22.423.349,49 United States \$22.909.628,84 \$14.548.359,36 \$12.686.090,53 \$7.874.990,12 \$35.595.719,36 \$22.423.349,49 Central \$3.008.359,55 \$4.581.925,77 \$1.323.769,09 \$2.537.218,11 \$4.332.128,64 \$7.119.143,87 Northeast \$2.867.904,65 \$4.581.925,77 \$1.059.022,59 \$2.537.218,11 \$3.926.927,24 \$7.119.143,87 Northwest \$5.735.364,53 \$4.581.925,77 \$3.925.645,58 \$2.537.218,11 \$9.661.010,11 \$7.119.143,87 Southeast \$2.432.293,36 \$4.581.925,77 \$1.181.754,12 \$2.537.218,11 \$3.614.047,47 \$7.119.143,87 Southwest \$8.865.706,75 \$4.581.925,77 \$5.195.899,15 \$2.537.218,11 \$14.061.605,90 \$7.119.143,87 Pacific \$3.881.215,18 \$13.997.909,91 \$3.310.788,70 \$8.602.987,45 \$7.192.003,88 \$22.600.897,35 Australia \$3.881.215,18 \$3.881.215,18 \$3.310.788,70 \$3.310.788,70 \$7.192.003,88 \$7.192.003,88 Grand Total \$41.993.729,72 \$41.993.729,72 \$25.808.962,34 \$25.808.962,34 \$67.802.692,06 \$67.802.692,06

MDX script for that is here:

```Create Member CurrentCube.[Measures].[Original Measure] AS
[Measures].[Sales Amount]
;

Create Member CurrentCube.[Measures].[Where are measures in Excel] AS
case
when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
else -1
end
;

Create Member CurrentCube.[Measures].[Where are non-measure rows] AS
iif(
NOT IsError(Axis(1).Count) AND
[Measures].[Where are measures in Excel] < 1,
1,
iif(
[Measures].[Where are measures in Excel] <> 0 AND
NOT IsError(Axis(0).Count),
0,
null
)
)
;

Create Member CurrentCube.[Measures].[Average Measure] AS
iif( IsEmpty([Measures].[Original Measure]),
null,
iif( IsEmpty([Measures].[Where are non-measure rows]),
[Measures].[Original Measure],
iif( Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
[Measures].[Original Measure],
Sum(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure])
/
Count(NonEmpty(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure]))
)
)
)
, FORMAT_STRING = 'Currency'
;```

Hope that's it this time :-).

Remarks ...

Limitations of the last measure (Average among siblings aka Average Measure) is that it is tuned to work in Excel, and that it expects only one hierarchy. As soon as crossjoin happens on axis, the results might look weird. But, as I understood, that is not your intention. You wish to be able to expand members. And that is supported, which can be seen above in the table. Btw, it will work for any hierarchy, not just one dimension's hierarchies. All hierarchies in any cube. As long as you stick to conditions (no crossjoins). Oh, another thing, empty rows are taken care of.

It is possible to adjust the last measure to work with multiple hierarchies, but I didn't wanted to complicate this any further. Maybe it appears one day on my blog, for those of you who are curious.

Have fun,

Tomislav Piasevoli
www.softpro.hr
• Marked as answer by Tuesday, January 19, 2010 3:34 PM
Friday, January 15, 2010 9:09 PM

All replies

• Hi Paul,
It looks like you are trying to find the average of the children within the hierarchy.  If this is true, then you can add another measure to your measure group and use the Average of Children function, rather than sum.  This would then be usable in any hierarchy.

On the other hand, your calculated member above returns the not the average for each member, but the average of all the members at the current level.  So every member will always show the same value.  This is a bit different from what I described above and might not fit your need at all.  If that's the case, you might try testing how the measure evaluates for other hierarchies in the same dimension, because it will probably still work.  The issue would be more for working in other dimensions.

Please let me know if I missing the point,
Mark
Friday, January 08, 2010 1:55 AM
• Hi Mark,

Yes your rihgt it is the average of all members so that we can compare that average against one customer from that group. I dont understand your suggestion " try testing how the measure evaluates for other hierarchies in the same dimension, because it will probably still work."what do you mean with this.

I would like to re-use this calculated member for a different hierachy (same dimension) in addition this same calculated member but then used by a different dimension.

rgrds,

Paul.
Saturday, January 09, 2010 10:12 AM
• Paul,
I found that when I create a percent of calculation for any attribute hierarchy, it will work for any other hierarchy in the same dimension.  Try querying your calculated member against other hierarchies in the same dimension and see how this works for you.  This will not help you in regards to other dimesions though, so you might create calculated measures that include the dimension name in their title.

I don't know any tricks though that could make this calculation more reusable.
Mark
Monday, January 11, 2010 1:52 AM
• Hi Paul,

You can try the function Axis(), something like this:

Axis(1).Item(0).Item(Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent

http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!436.entry

Although it related to percentage calculation, but you can still try the same way to achieve AVG.

But, generally, we create AVG measure like this:

Create a physical measure with function count, create a physical measure with function sum, and then create a calculated measure with measures.sum/measures.count. It will works for all hierarchy. You can refer to the [Internet Average Sales Amount] in the sample cube supplied by Microsoft.

Hope this helps.

Raymond

Monday, January 11, 2010 5:03 AM
• can i use this "measures.sum/measures.count" but then i need the count of childeren. can you help me with the syntax becuase #Value keeps appearing.
Tuesday, January 12, 2010 3:38 PM
• Hi,

may I put a few lines here.

It's true that this can be handled in many ways.

One way is to define and use 2 regular measures and combine them in a ratio as a new calculated measure. While we already have the first measure, the "sum" one, nobody was precise enough about the second measure, the "count" one. That measure has to be of DistinctCount-type. Furthermore, it should be based on the key attribute of Component dimension (that particular field, but in a fact table). The new calculated measure, defined as a ratio of sum over distinct count would yield the result required. Unless average is ment as something else.

The next approach is the one with Axis() function. While feasible, it is not required in this case because here we have a request for general solution of average calculation only on a particular dimension (its hierarchies, no matter which).

I'd like to propose the third approach. It should work just like the first one, but doesn't require modification of the cube structure (new regular measures). It is MDX script-based only.

If the aggregate function used for the measure Flight Cycles was Sum (I hope it is), then we should be able to use the key attribute of that dimension and test for existance of members in it. That will be our general solution. The downside is - it is much slower solution when the dimension is huge. The first solution is recommended in that case.

And the expression for it? Here it is:

```Sum( Existing [Component].[Component].[Component].MEMBERS, [Measures].[Flight Cycles] )
/
Count( NonEmpty( Existing [Component].[Component].[Component].MEMBERS, [Measures].[Flight Cycles] ) )```

Existing operator should return only those leaf members (Component attribute in this case I presume) that are in the context at the moment (per each cell evaluation). Sum of them is our numerator. The denominator is obtained similarily, except we used NonEmpty() to remove (existing) members without value for current context.

But ...

It seems the ratio (average) in this case should be based on the parent and siblings (not all members of the same level, just some of them). Which means only the second solution prevails, all other become too complex if not impossible. Why? Well, we do not know which parent are we're after. Hence Axis() as the only solution :-(, the most heaviest one. Don't get me wrong, it requires fine-tuning too, but remains possible.

The key for modifying the Axis() solution is to provide .Parent somewhere in the upper part of the ratio, and .Siblings in the lower part (Siblings equals Parent.Children but is error-proof when dealing with root member - Parent hits the ceiling :-)). Where exactly should not be hard if you get it to work in the first place, without modifications. Here's the barebone solution, taken from Raymond's post.

```(
Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Parent,
[Measures].[Flight Cycles]
)
/
Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Siblings.Count```

It says: give me the ratio of a tuple (no need for sum) over count of siblings for the current member, where the tuple is equivalent to parent's value for the measure of choice (here Flight Cycles).

Take care of division by zero, though.

Limitations? It expects the hierarchy of that dimension on rows. To be precise, as the first one there (in case there's a crossjoin).

Since this is probably oversimplified for the client of your choice, take a look at links provided by Raymond earlier. They should guide you how to achieve more bullet-proof solution.

Hope that's it,

Tomislav Piasevoli
www.softpro.hr
Tuesday, January 12, 2010 10:08 PM
• hi Tomislav,

I did try what you have suggested but the calculated member returns empty.

CREATE

MEMBER CURRENTCUBE.[Measures].[AxisTestcase] AS

(

Axis

(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Parent,

[Measures].[Flight Hours]

)

/

Axis

(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Siblings.Count

 Manufacturer Type Make Flight Hours AxisTestcase Airbus SAS 73,689.10 Boeing - Boeing Commercial Airplane Group 47,883.07 Bombardier 39,644.72 Embraer Embraer ERJ 190 100 12,291.65 Total 12,291.65 Total 12,291.65 Not Applicable 274.04 Grand Total 173,782.57

Wednesday, January 13, 2010 6:14 PM
• Hi Tomislav,

I did use the code described on your blog and replaced the measure with Flight Hours, Im not a die hard with MDX in particulair but what needs to be changed in order to get the count of siblings in the code.

regards,

Paul.
Wednesday, January 13, 2010 6:39 PM
• OK, just before I do that, tell me what client/front-end are you using? It matters a lot.

Regards,

Tomislav Piasevoli
www.softpro.hr
Wednesday, January 13, 2010 7:21 PM
• The end user will use Excel 2007 im using visual studio 2005
Thursday, January 14, 2010 8:15 AM
• Ok,

the expression I gave you didn't work because it was a simplified case which works only when you have one hierarchy on rows.

I have to adjust the script from my blog in order to fit this scenario. Will take some time. In the meantime, draw me the result table the way you want it to act. See the blog article, the bitmap table.

For example, if there are 3 hierarchies on rows, how should the average be calculated in case there's a root member and in case there's a non-root member, per each hierarchy. Meaning, provide me with 000 ... 111 matrix where you define a formula (in words or simbols will be ok) for each combination. That way I can adjust the script. OK?

Example (0 represents non-root member, 1 represents root member):

110 = 1, 1, 0.Parent/0.Siblings (meaning - calculate the average only for the last hierarchy)
100 = 1, 0, 0.Parent/0.Siblings (or is it 1, 0.Parent/0.Siblings, 0.Parent/0.Siblings?) (see, don't know how you want this to work)

See what I mean? Same number means take it as is, ".Something" means apply a function. Several functions form a formula for that combination/hierarchy.

That table will save me from making huge scripts over and over again until I match what you had in mind. Therefore, I suggest the opposite - let's design first and code later :-).

Regards,

Tomislav Piasevoli
www.softpro.hr
Thursday, January 14, 2010 8:25 PM
• Hi,

I dont understand what you mean with number of hierachies on rows? sorry but im struggling with semantics sometimes. I need one hierachie which consist out of 3 attributes ie. manufacture, type and make.

This hierachie will be placed in the rows (only one).

the purpose of having the sum of the parent and count of the childeren is to determine what the average is per child (will be the same number) and compare it to the actual flight hours of a certain child. (the measure of the fact)

sum(MANUFACTURER)/sum(count(nonempty(TYPE) AND sum(TYPE)/sum(count(nonempty(MAKE)
basicly i want the sum of the parent devided by the sum of non empty count of its childeren.

Sorry i have to put this in word because i dont follow the matrix with the ones and zero's
On top of this all i want a date dimension in the columns.

hope this will help

thx

Paul
Friday, January 15, 2010 6:43 PM
• OK, OK, maybe I was asking to much. Sorry, I'll try to keep this as simple as possible.

Let me see if I got it right.

You have a hierarchy on rows which you can expand (drilldown) to lower levels. And you want a calculation that works fine for one hierarchy as well as for another hierarchy of that same dimension.

What confuses me are your "parent" and "child" terms.

From what I've concluded, there are only currentmember and his siblings.

When you say sum of parent, that should be understood as the sum of currentmember's siblings. When you say children, my guess is that you automatically position yourself on that parent and you're looking at his children. But, in reality, the coordinate is still on currentmember (he is the one who's parent we just referred to) and therefore those children should be understood as his siblings. Only this time we're taking their count. Sum over count is an average. Average among siblings would be the proper terminology in this case if I'm not mistaken. So, that's what we're after!

Now, let's do some coding ...

Is this it?

 Column Labels CY 2003 CY 2004 Total Original Measure Total Average Measure Row Labels Original Measure Average Measure Original Measure Average Measure Europe \$9.015.795,82 \$13.997.909,91 \$6.748.193,39 \$8.602.987,45 \$15.763.989,21 \$22.600.897,35 France \$3.400.129,00 \$3.005.265,27 \$2.298.789,76 \$2.249.397,80 \$5.698.918,77 \$5.254.663,07 Germany \$2.157.272,42 \$3.005.265,27 \$1.962.012,12 \$2.249.397,80 \$4.119.284,54 \$5.254.663,07 United Kingdom \$3.458.394,40 \$3.005.265,27 \$2.487.391,50 \$2.249.397,80 \$5.945.785,90 \$5.254.663,07 North America \$29.096.718,73 \$13.997.909,91 \$15.749.980,25 \$8.602.987,45 \$44.846.698,97 \$22.600.897,35 Canada \$6.187.089,89 \$14.548.359,36 \$3.063.889,72 \$7.874.990,12 \$9.250.979,61 \$22.423.349,49 United States \$22.909.628,84 \$14.548.359,36 \$12.686.090,53 \$7.874.990,12 \$35.595.719,36 \$22.423.349,49 Central \$3.008.359,55 \$4.581.925,77 \$1.323.769,09 \$2.537.218,11 \$4.332.128,64 \$7.119.143,87 Northeast \$2.867.904,65 \$4.581.925,77 \$1.059.022,59 \$2.537.218,11 \$3.926.927,24 \$7.119.143,87 Northwest \$5.735.364,53 \$4.581.925,77 \$3.925.645,58 \$2.537.218,11 \$9.661.010,11 \$7.119.143,87 Southeast \$2.432.293,36 \$4.581.925,77 \$1.181.754,12 \$2.537.218,11 \$3.614.047,47 \$7.119.143,87 Southwest \$8.865.706,75 \$4.581.925,77 \$5.195.899,15 \$2.537.218,11 \$14.061.605,90 \$7.119.143,87 Pacific \$3.881.215,18 \$13.997.909,91 \$3.310.788,70 \$8.602.987,45 \$7.192.003,88 \$22.600.897,35 Australia \$3.881.215,18 \$3.881.215,18 \$3.310.788,70 \$3.310.788,70 \$7.192.003,88 \$7.192.003,88 Grand Total \$41.993.729,72 \$41.993.729,72 \$25.808.962,34 \$25.808.962,34 \$67.802.692,06 \$67.802.692,06

MDX script for that is here:

```Create Member CurrentCube.[Measures].[Original Measure] AS
[Measures].[Sales Amount]
;

Create Member CurrentCube.[Measures].[Where are measures in Excel] AS
case
when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
else -1
end
;

Create Member CurrentCube.[Measures].[Where are non-measure rows] AS
iif(
NOT IsError(Axis(1).Count) AND
[Measures].[Where are measures in Excel] < 1,
1,
iif(
[Measures].[Where are measures in Excel] <> 0 AND
NOT IsError(Axis(0).Count),
0,
null
)
)
;

Create Member CurrentCube.[Measures].[Average Measure] AS
iif( IsEmpty([Measures].[Original Measure]),
null,
iif( IsEmpty([Measures].[Where are non-measure rows]),
[Measures].[Original Measure],
iif( Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
[Measures].[Original Measure],
Sum(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure])
/
Count(NonEmpty(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure]))
)
)
)
, FORMAT_STRING = 'Currency'
;```

Hope that's it this time :-).

Remarks ...

Limitations of the last measure (Average among siblings aka Average Measure) is that it is tuned to work in Excel, and that it expects only one hierarchy. As soon as crossjoin happens on axis, the results might look weird. But, as I understood, that is not your intention. You wish to be able to expand members. And that is supported, which can be seen above in the table. Btw, it will work for any hierarchy, not just one dimension's hierarchies. All hierarchies in any cube. As long as you stick to conditions (no crossjoins). Oh, another thing, empty rows are taken care of.

It is possible to adjust the last measure to work with multiple hierarchies, but I didn't wanted to complicate this any further. Maybe it appears one day on my blog, for those of you who are curious.

Have fun,

Tomislav Piasevoli
www.softpro.hr
• Marked as answer by Tuesday, January 19, 2010 3:34 PM
Friday, January 15, 2010 9:09 PM
• Thanks Tomislav,

You were a great help, it works like a charm :-) I have created a KPI which work as well but now it will show a KPI for a line without any data, do you know how this can be solved? pls see SN: 1571 this is NOT showing up when i browse on the cube without the KPI but when i select the KPI in excel it will show this empty lines.

Should NON empty behaviour be included in the KPI syntax?

value expression:[Actual FH/FC Ratio]-[Average FH/FC Ratio]
Goal expression: [Average FH/FC Ratio]

Case

When

KpiValue("FH FC Ratio Variation") /KpiGoal("FH FC Ratio Variation") >=.15

Then 1

When

KpiValue("FH FC Ratio Variation") /KpiGoal("FH FC Ratio Variation") <.15 And

KpiValue("FH FC Ratio Variation") /KpiGoal("FH FC Ratio Variation") >=-.15

Then 0

Else-1

End

 Airbus SAS 73,689.10 37,154 34,756.51 20,419.20 1.98 1.70 Airbus A319 7,945.23 6,958 18,422.27 9,288.50 1.14 1.98 Airbus A320 30,990.61 16,845 18,422.27 9,288.50 1.84 1.98 200 30,990.61 16,845 30,990.61 16,845.00 1.84 1.84 Airframe for A320 PN:AIRFRAME [SN:0052] 5,319.43 2,423 2,817.33 1,531.36 2.20 1.84 Airframe for A320 PN:AIRFRAME [SN:0136] 5.93 7 2,817.33 1,531.36 0.85 1.84 Airframe for A320 PN:AIRFRAME [SN:1571] Airframe for A320 PN:AIRFRAME [SN:2569] 3,743.80 2,049 2,817.33 1,531.36 1.83 1.84 Airframe for A320 PN:AIRFRAME [SN:2584] 3,657.07 2,057 2,817.33 1,531.36 1.78 1.84 Airframe for A320 PN:AIRFRAME [SN:2792] 3,606.20 2,003 2,817.33 1,531.36 1.80 1.84 Airframe for A320 PN:AIRFRAME [SN:2817] 250.00 100 2,817.33 1,531.36 2.50 1.84 Airframe for A320 PN:AIRFRAME [SN:2822] 3,541.28 1,984 2,817.33 1,531.36 1.78 1.84 Airframe for A320 PN:AIRFRAME [SN:2856] Airframe for A320 PN:AIRFRAME [SN:3016] 3,659.82 2,037 2,817.33 1,531.36 1.80 1.84 Airframe for A320 PN:AIRFRAME [SN:3349] 3,667.58 2,110 2,817.33 1,531.36 1.74 1.84 Airframe for A320 PN:AIRFRAME [SN:3919] 1,838.39 1,057 2,817.33 1,531.36 1.74 1.84 Airframe for A320 PN:AIRFRAME [SN:3939] 1,701.12 1,018 2,817.33 1,531.36 1.67 1.84 Airbus A321 32,697.73 13,010 18,422.27 9,288.50 2.51 1.98 Airbus A330 2,055.52 341 18,422.27 9,288.50 6.03 1.98 Boeing - Boeing Commercial Airplane Group 47,883.07 16,862 34,756.51 20,419.20 2.84 1.70 Bombardier 39,644.72 39,210 34,756.51 20,419.20 1.01 1.70 Bombdr CRJ 31,087.08 31,697 19,822.36 19,605.00 0.98 1.01 DHC Dash 8 8,557.64 7,513 19,822.36 19,605.00 1.14 1.01 Embraer 12,291.65 8,684 34,756.51 20,419.20 1.42 1.70 Not Applicable 274.04 186 34,756.51 20,419.20 1.47 1.70 Grand Total 173,782.57 102,096 173,782.57 102,096.00 1.70 1.70
Tuesday, January 19, 2010 3:40 PM
• Good, I'm glad it works.

KPI Status is not defined correctly. You should wrap it like this:

`iif( IsEmpty( [Measures].[Original Measure] ), null, <your case comes here> )`

Which means you've forgot to test for emptiness.

Regards,

Tomislav Piasevoli