I need to calculate a weighted average for a group of members
identified by the intersection of some named sets.

For background lets say I have the following Dimensions...
[Time] (with levels Year, Quarter and Month)
[Department] (with one level Department)
[Portfolio] (With 3 levels Corporation, Company, Portfolio)
and the following measures
[Yield] and [Cost]

The MDX is dynamically built by my application based on the values
picked by the users. (i.e. they can pick a set of months, a set of
departments and a set of Portfolios)

The MDX I am generating looks like...
WITH
SET [TIME SET] AS '{ Descendants([2003], [Time].[Month]) } '
SET [DEPARTMENT SET] AS '{
[Department].[ABS],[Department].[AFC],[Department].[ALT] }'
SET [ALLCORP SET] AS '{ [Portfolio].[Corporation].[ALCORPHOLD] }'
SET [CONSPL SET] AS '{ [Portfolio].[Corporation].[CONSPL] }'
SET [LIFEANDCAP SET] AS '{ [Portfolio].[Corporation].[LIFEANDCAP] }'
SET [PORTFOLIO SET] AS '{ [ALLCORP SET], [CONSPL SET], [LIFEANDCAP
SET] } '
MEMBER [Measures].[Wgtd Avg] AS 'AVG(CROSSJOIN( {[PORTFOLIO SET]},
{[DEPARTMENT SET]} ), (Iif([MEASURES].[Cost] = 0, 0, [MEASURES].[Cost]
) * [MEASURES].[Yield]) / Iif([MEASURES].[Cost] = 0, 1,
[MEASURES].[Cost])) '
SELECT { [TIME SET] } ON COLUMNS,
NON EMPTY {[Measures].[Wgtd Avg]} ON ROWS
FROM [PLAN MEP]

I am getting the format I want, one row with a wgtd avg for each month
in 2003. Unfortunately, the resulting values being returned for the
Wgtd Avg member are way too low. I am guessing that somehow the MDX is
pulling in 0 values or something.

Any help is appreciated.

Assuming that [Wgtd Avg] is an average Yield, weighted by Cost, there
are a couple of issues in the query below:

- Avg() will perform a simple, not weighted average

- When Cost = 0, the value considered is 0, not Yield


Try weighted average by dividing Sums instead, like:
'SUM(CROSSJOIN({[PORTFOLIO SET]},{[DEPARTMENT SET]}),
([MEASURES].[Cost] * [MEASURES].[Yield])) /
Iif(SUM(CROSSJOIN({[PORTFOLIO SET]},{[DEPARTMENT SET]}),
[MEASURES].[Cost]) = 0, 1,
SUM(CROSSJOIN({[PORTFOLIO SET]},{[DEPARTMENT SET]}),
[MEASURES].[Cost]))'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Wonderful - the change to my MDX worked.

Thanks!!!

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Try this, I hope it works. I have read many places not to use the AVG fuction but instead use the SUM function and the Count. First try to get the SUM of [MEASURES].[Cost]) * [MEASURES].[Yield]) for your set and then divide that number by the size of your set using COUNT.


MEMBER [Measures].[Wgtd Avg] AS

'SUM (CROSSJOIN( {[PORTFOLIO SET]}, {[DEPARTMENT SET]} ),
(Iif([MEASURES].[Cost] = 0, 0, [MEASURES].[Cost]
) * [MEASURES].[Yield])
))

/ COUNT (CROSSJOIN( {[PORTFOLIO SET]}, {[DEPARTMENT SET]}))'

Good Luck,

Shabnam

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...