How to do weighted average in Cube Calculation?

I am struggling with creating a weighted average calculation in my SSAS 2005 cube. I have a calculated member that computes the average Height of my products. This works out fine when I slice that measure up by Product Code. However, when I use it in another calculation, it appears to be defaulting to the ALL member.

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.