innocent1973
asked on
calculated member on a dimension
In SSAS I have deployed a cube named HCSALES.This cube has 4 measures such as:
- UnitSold
- SalesValue
- UnitReturn
- ReturnValue
Now I want to add a calculated member bu using my DimProduct dimension. This dimension has an attribute named ITPP. And my desired calculated member formula is like this:
SalesValue - (ITPP x UnitSold)
How can do this? Any idea?
Please help
P.S
I have tried to do it several times. I have also attached a screenshot
screenshot.png
- UnitSold
- SalesValue
- UnitReturn
- ReturnValue
Now I want to add a calculated member bu using my DimProduct dimension. This dimension has an attribute named ITPP. And my desired calculated member formula is like this:
SalesValue - (ITPP x UnitSold)
How can do this? Any idea?
Please help
P.S
I have tried to do it several times. I have also attached a screenshot
screenshot.png
The suggest pratice from microsoft is to set the dimension also as Measure Group. Just click on the dimension table on cube design and set it as measre group. Then you can work with attribute ITPP as a measure!
Try it!
Regards.
Pedro
CBIP, MVP
Try it!
Regards.
Pedro
CBIP, MVP
ASKER
Hello Pedro,
I have tried to apply the dimension attribute as a measure by using "new measure group" function. But I get an error such as:
"Object variable not set to an instance of an object". I have attached this screenshot as a png file.
And TimHumphries,
Thank you very much also to you. I have tried to apply your code snippet. But after cube processing all the calculated measure values are like these:
#VALUE
Is there anything wrong?
screen2.png
screen3.png
I have tried to apply the dimension attribute as a measure by using "new measure group" function. But I get an error such as:
"Object variable not set to an instance of an object". I have attached this screenshot as a png file.
And TimHumphries,
Thank you very much also to you. I have tried to apply your code snippet. But after cube processing all the calculated measure values are like these:
#VALUE
Is there anything wrong?
screen2.png
screen3.png
Hi,
As I stated, if you are using ITTP in the calculation, then it will only be valid of there is a currentmember value for the hierachy in the query. I can't see this dimension in your browser which is probably why the value is not being calculated.
Tim
As I stated, if you are using ITTP in the calculation, then it will only be valid of there is a currentmember value for the hierachy in the query. I can't see this dimension in your browser which is probably why the value is not being calculated.
Tim
ASKER
Thank for your reply Tim,
The ITTP calculate member is always #VALUE. And tooltiptext is:
Parser: the syntax for "All" is incorrect
The ITTP calculate member is always #VALUE. And tooltiptext is:
Parser: the syntax for "All" is incorrect
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your patient.. It works like a charm. GREAT!!!
TimHumphries, Thank you too
TimHumphries, Thank you too
The value will only be calculable when there is a current value for the DimProductMAX dimension.
Try using [Measures].[SalesValue] - (DimProductMAX.ITPP.Curren
Tim