Proc OLAP: MDX Order of Operations for Weighted Average

[StephenOverton]

I'm trying to compute a weighted average in an OLAP cube using a calculated member with some MDX logic but the order of operations is throwing me off and basically forcing a simple average calculation rather than using the weights. This is because the cube is aggregating the measures used in the MDX calc FIRST, then doing the math for the weighted average. I'm looking for a way to force the MDX calculation to do the weighted avg multiplication first, then do the sum aggregation.

Example:
In my dataset I've got mortgage loan-level information with things like current balance, LTV, DTI, etc. I need to create a weighted average of LTV, DTI, or any other variable using the Current Balance as the weight.

So logically the math would be this for Weighted Avg LTV:
1) at the loan level, multiply current balance by LTV to get the weighted LTV
2) sum the weighted LTV across all of the loans (or in the case of OLAP, whatever dimensions are being crossed)
3) sum the current balance (in the case of OLAP, whatever slice)
4) divide step 2 by step 3
5) Viola, weighted average

Any insight is appreciated. The problem is simple, its just figuring out how to control how the calculated member does its math. I could easily do the multiplication in the base dataset and calculate the weighted values but I'd rather do them in the cube.

This is the example define code in the proc OLAP statement along with the measure definitions. The first member define is the problem. I need to force it to NOT do the measure aggregation first. I think I need some function outside of the multiplication.

MEASURE CURR_LOAN_BAL_AMTSUM
STAT = SUM
COLUMN = CURR_LOAN_BAL_AMT
CAPTION = 'Current Loan Balance'
FORMAT = dollar22.2;
MEASURE ORIG_COMB_LTVMAX
STAT = MAX
COLUMN = ORIG_COMB_LTV
CAPTION = 'Combined LTV'
FORMAT = comma14.4;

/* Numerator for weighted average */
DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd CLTV]' AS
'([MEASURES].[CURR_LOAN_BAL_AMTSUM] * [MEASURES].[ORIG_COMB_LTVMAX]), FORMAT_STRING="COMMA15."';

/* Weighted average calculation */
DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd Avg CLTV]' AS
'([MEASURES].[Wtd CLTV] / [MEASURES].[CURR_LOAN_BAL_AMTSUM]), FORMAT_STRING="COMMA15.4"';

[Patrick]

Hi

In my experience MDX skills are a rather rare good in the SAS world.

Do you have the "ORION" cubes at hand? And would it be possible to simulate your problem there? That would help a lot in searching for a solution.

Thanks
Patrick

P.S: What Olap Cube Studio version are you using? Message was edited by: Patrick

[StephenOverton]

I figured out the solution using IIF logic in an MDX statement. BUT the hard part with this is you have to detect every possible dimension you want to compute the weighted average for. So I took the easy route and simply put the weights in the source dataset. Worked like a charm after that!