Hi,
I have a cube with the four dimensions that are shared and the MIN,AVG,and
MAX Commission Rate. When I add the Customer to the Role Field and add the
rest of the dimensions to the Page Field, the measures are aggregated, which
is incorrect displayed. However, if I select a member from each dimension in
the page field, then the rate are correctly displayed. Another thing I
noticed is when I switch the Country and Customer dimension, the rate are
aggregated and not displayed correctly. I think I need a customer rollup but
do not know how to go about it without affecting the shared dimension. Also,
the measures (Min, AVG, MAX) are being source from an Oracle View that is
performing these functions because of the AVG formula, instead of the cube.
The AVG Commission Rate is calculated as

AVG Commission Rate = sum(commission rate * principal amt) / sum(principal
Amt)

If the unit of measure is CPS, then it is done like this

AVG Commission Rate = sum(commission rate * quantity)/sum(quantity)

I can definitely use some guidance and/or help in resolving this
issue...perhaps, I should be doing calculation in the cube and just feed the
raw data.


DIMENSIONS
---------------
CUSTOMER
PERIOD
OFFICE
COUNTRY

MEASURE
------------
MIN COMMISSION RATE
AVG COMMISSION RATE
MAX COMMISSION RATE

Based on the problem description and assuming that you're using AS 2000,
you might be able to use derived measures in the cube for the averages,
rather than computing them in the Oracle view. For example, you could
create a "sum" measure like Commission, with the Source Column property
defined as:

commission rate * quantity

And another "sum" measure like Quantity, simply based on the quantity
field. Then, the AvgCommissionRate would be:

[Measures].[Commission]/[Measures].[Quantity]


Derived vs. calculated measures for AS 2000 are discussed here:

http://www.databasejournal.com/featu...le.php/3394681
vs. Calculated Measures

By William Pearson
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
Thanks so much for the quick respond...I do appreciate your help and
assistant. I initially started to do the calculation in the cube but ran
into problems. I am going to make another attempt.

The results of finding the average depends on the unit of measure. If the
unit of measure is BPS, I then have to divide by principal amt. I will try
to come up with the formula.

The other thing is how can I prevent and implement the calculation on a
different dimension.

For example, if I select the month, the country, the office, and show all
customer per row, then the calculation and result display is correct. But
when I only want to show the measure for only countries, it is aggregated.

What I need the cube to do is to apply the formula at the country level and
not the customer level. Let me know, if you need me to send you a screen
print to illustrate.

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Deepak,
I am trying to create the commission calculated member but getting errors.
Here is the formula

Commission = Iif([UOM].[UOM].CurrentMember = 'BPS', ([Measures].[Tot Comm
Rate] *[Measures].[Principal Amt]), Iif([UOM].[UOM].Current =
'CPS',[Measures].[Tot Comm Rate] * [Measures].[Quantity]))

"Deepak Puri" wrote:

Deepak,
I got it to work...I think!

Commission = (IIF([UOM].CurrentMember.Name = "BPS", [Measures].[Tot Comm
Rate] * [Measures].[Principal Amt], IIF(UOM.CurrentMember.NAME = "CPS",
[Measures].[Tot Comm Rate] * [Measures].[Quantity],NULL)))

"fsanchez" wrote:

Hi Deepak,
I created the measures that you suggested...

Total Commission Amt = [Measures].[Tot Comm Rate] *
(IIF([UOM].CurrentMember.Name = "BPS", [Measures].[Principal Amt],
IIF(UOM.CurrentMember.NAME = "CPS", [Measures].[Quantity],0)))

But I don't think it is working correctly because I am getting in the cube
for a particular customer $963,748,366.34 and when I check the database
through SQL, I am getting $107,083,151.

I created two additional measure to calculate the Average...

Total Principal Amt = ([Measures].[Principal Amt], [PERIOD].CurrentMember)

Total Quantity = ([Measures].[Quantity], [PERIOD].CurrentMember)

Avg Commission Rate = (IIF([UOM].CurrentMember.Name = "BPS",
[Measures].[Total Commission Amt]/ [Measures].[Total Principal Amt],
IIF(UOM.CurrentMember.NAME = "CPS", [Measures].[Total Commission Amt] /
[Measures].[Total Quantity],NULL)))

Do you know what I am doing wrong? Also, I need to implement a different
rollup for Country;

Please let me know if you need some screen prints...

Regards,
Fernando Sanchez

"Deepak Puri" wrote: