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.
