Hi,
I have a DEAL dimension that has several member proproties (Base Rate,
Spread Rate, etc.). I want to calculate the min, max, and avg for each of
the member properties.

I have created a Deal Count and the following calculated measure but I get
an error back.

Avg Commission Rate = [DEAL].Properties("Base Rate") / [Measures].[Deal Count]

What is the error?

I believe you may need to cast the returned value into a typed value either
by using:
Avg Commission Rate = [DEAL].Properties("Base Rate", TYPED) /
[Measures].[Deal Count]
or
Avg Commission Rate = CDBL( [DEAL].Properties("Base Rate") ) /
[Measures].[Deal Count]

The Properties method returns strings and you would need to either obtain
the raw data type or cast the string to a numeric data type...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"fsanchez" <fsanchez (AT) discussions (DOT) microsoft.com> wrote

Hi Akshai,
I will try that but the error I am getting just say ERR when browsing the
data..

"Akshai Mirchandani [MS]" wrote:

divide by 0 error? If so, use conditional statement to avoid this.

"fsanchez" wrote:

Hi Yongli,
I created the following measure to see what I am getting back...

Commission Rate = IIF(IsEmpty(CDbl([DEAL].CurrentMember.Properties("Base
Rate"))),NULL, CDbl([DEAL].CurrentMember.Properties("Base Rate")))


All of the values are -2,147,467,259 and not sure why I am getting this.

When I create a measure as followed, I get back null values.

Avg Commission Rate = IIF(IsEmpty(CDbl([DEAL].CurrentMember.Properties("Base
Rate"))),NULL,IIF(CDbl([DEAL].CurrentMember.Properties("Base Rate")) <=
0,NULL,CDbl([DEAL].CurrentMember.Properties("Base Rate")) / [Measures].[Deal
Count]))

"yongli" wrote:

It looks like that there are something wrong with the field "Base Rate".
What's the origianl data type for the field? if this is non-numerical, you
have to make sure to get rid of invalid characters before converting to
numerical type.

"fsanchez" wrote:

Hi Yongli,
all of the fields are numeric, NUMBER(19,4). However, it is not part of the
fact table. These properities are part of the deal detail dimension.


There are seven dimensions/tables joined to the fact table and they are:
DW_CHARGE_TYPE
DW_INDUSTRY_SEGMENT
DW_SALES_PERSON_VW
DW_SALES_TRADER_VW
DW_CUSTOMER
DW_PERIOD
DW_COUNTRY

I tried something different and created an Oracle View that extracts the
deal rates from the DW_DEAL_DETAIL table and named it
DW_CUSTOMER_DEAL_RATE_FACT, which contains

DW_COUNTRY
DW_CUSTUMER
DW_PERIOD
BASE_RATE
CREDIT_RATE
SHARED_RATE
SPREAD_RATE
OTHER_RATE

I created measure called Min Commission Rate using the BASE_RATE but changed
the aggregate functioin to MIN. I also created a Max Commission Rate and for
the Commissioin Rate, I dropped the BASE_RATE again and just renamed it. For
the Avg Commission Rate, I created the following Calculated Member:

AVG COMMISSION RATE = [Measures].[Commission Rate] / [Measures].[Deal Count]

This seem to work fine but it aggregates the rates for MIN, MAX, and the
AVG, which I don't want to.
yongli" wrote:

Hi Yongli,
I want to expand on my respond from Yesterday. When I am looking at the
Min, Max, and Avg measures for All Period and Office, I don't want the
Analysis Services to aggregate the values. I want to see the Min, Max, and
Avg for all the periods.

Regards,
Fernando

"yongli" wrote:

If you don't want to see AS to aggregate the values, just define a calucated
member or customer roll-up formula to override the values.

"fsanchez" wrote:

Hi Yongli,
Can you provide me an example in how to formulate this calculated member?

"yongli" wrote: