Weighted Average in cube

[mzimmpa]

Hello all:

New to mdx.
I am having a terrible time creating MDX query to resolve the weighted average.

I have an "Officer" dimension with following levels

CREATE TABLE [dbo].[Officer_Dim] (
[Holding_Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL.
[Officer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

Holding Company is highest level.
Officer is the lowest level.


"Risk" Dimension has following levels:

CREATE TABLE [dbo].[Risk_Score_Dim] (
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sub_Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Risk_Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Risk_Score] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

Category is highest level, Risk_Score is lowest.

Also have a Measure, which is "Principal Balance".

I need to do a WEIGHTED average of OFFICER by RISK SCORE, weighted on (measure) prinicpal balance.

Calcualtion would be as follows:

sum(Principal Balance * Risk_Score)/sum(Total Principal Balance for OFFICER)

Should I create a calculated measure for sum(Principal Balance * Risk_Score), then use this in an MDX query?

Should I do the same for sum(Total Principal Balance for OFFICER) ?

Any example or help will be GREATLY appreciated.
Having problem understanding an advanced mdx query and how to apply such a query in the cube editor.

Thanks again!

Sincerley,

mzimm

 

[netDev007]

I'm no MDX expert, but I'd suggest you need to consider the "dimensionality" of your fact table (details of which are scant or missing in your posting)
Presumably the principal balance exists for a period of time. Does your fact table have a row for each day? or does it just change when principal balance changes? Normally a weighted average would compensate for this sort of thing...a balance that existed for 10 days is 10 times as important as one that existed for 1 day etc...
I'm work in insurance, and weighted average is something I've wrestled with.
Remember when your physics teacher said "UNITS!" - meaning units must reconcile...well I've found it helps to work out the units of what you've been asked to measure.
For example:

sum(Principal Balance * Risk_Score) /
sum(Total Principal Balance for OFFICER)

is in Risk dollars per person (am I right?)