Dear All,

I'm in the process of removing some calculated members in my cube and
change them into "Derived Measure".

I have a calculated member: UnitCost(Budget) with the following
formula:
iif([Measures].[Budget Volume]=null,null,[Measures].[Budget Total
Cost]/[Measures].[Budget Volume])

When I created the new measure following the above calculated member, I
entered this formula into the source column:
CASE WHEN "dbo"."XX_SalesBudget"."BudgetVolume" = 0
OR "dbo"."XX_SalesBudget"."BudgetVolume" IS NULL THEN 0
ELSE
"dbo"."XX_SalesBudget"."BudgetTotalCost"/"dbo"."XX_SalesBudget"."BudgetVolume"
END

The problem is: the new measure return incorrect value. I don't know
where it is coming from but it's not BudgetTotalCost/BudgetVolume.

This problem also happens when I do multiplication. Addition &
Substraction are fine.
Anyone has experienced this before? Is this a bug?

TIA
Josephine

Have you tried test "isEmpty([Measures].[Budget Volume]) or
[Measures].[Budget Volume] = 0" instead of "[Measures].[Budget
Volume]=null"?

Hi..thanks for your input.

Unfortunately it didn't work. I got a 'The column is not valid' msg.

Any other ideas?

Josephine

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

Can you give some examples of the fact data, and of what results you
expected versus actually got?


- Deepak

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

Dear Deepak,

Here's an example

BudgetTotalCost=27743
BudgetVolume=1205
The correct UnitCostInternal(budget) should be 23.023(27743/1205)
But the value I got is 393.34

TIA
Josephine



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

Hi Josephine,

This result is rather puzzling - the easiest way to figure it out may be
drill-through, as suggested earlier here. From the fact records
returned, it may turn out that there is some problem in the cube schema
joining the tables:

http://groups-beta.google.com/group/...rver.olap/msg/
2d6ae220b5576feb

Newsgroups: microsoft.public.sqlserver.olap
From: "Mosha Pasumansky [MS]" <mos... (AT) online (DOT) microsoft.com> - Find
messages by this author
Date: Mon, 20 Dec 2004 00:59:36 -0800
Local: Mon, Dec 20 2004 12:59 am
Subject: Re: Cube showing slightly different data than in data
source...?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

I think the best way to troubleshoot it is to do DrillThorugh on the
cell
which shows wrong numbers - and track down the records in the fact table
that contributed to it.


--


==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

==================================================

- Deepak

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