How to do this calculation?

I have 3 tables , these table contain field as follow

- Dim_Product

- DecVolume

- Dim_Category

- DecVolume

- Fact_SalesTrx

- DecUnitPrice

If i would like to calculate a Price Per Unit with this formula, how should i do it?

Price Per Unit for specific Unit of Measurement (UOM)= (Unit Price/ UOM for that product) * UOM for that Category

I had try to do in the Calculation tab of the cube, but the result generate is weird (= Infinity).

This is the script that i had try

--

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Price Per Unit]

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume])*[Dim Category].[Dec Volume],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Dec Unit Price] },

VISIBLE = 1 ;

--

Appreciate some one can leave some clue here for me to go on...

Thanks

Woon How

You can do that inside datasourceview as named calculation!

NamedCalculation_PricePerUnit = (UnitPrice/DecVolume)*DecVolume

Try it.

Helped?

regards!

|||

Assuming that the "Value Column" Source and DataType have been correctly configured for the [Dim Product].[Dec Volume] and [Dim Category].[Dec Volume] attributes, you could try .MemberValue, like:

Code Snippet

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume].MemberValue)

* [Dim Category].[Dec Volume].MemberValue,

|||

Thanks for your hlp

Do you mean:

"Value Column" - mean insert some data in? (I'm had put some value for this column)

"Data Type: - mean set the data type ? )(I'm created it in design and set it to decimal)

I try had you approach and result return "#VALUE!". and i highlight the cell, it mention type mismatch.

Any idea?

Thanks In Advance Deepak Puri..

|||

Thanks PedroCGD,

If doing in this way, i would need to create another 2 columns into the Fact_SalesTrx Tables (decVolume Of Product,DecVolume Of Category)

Would that be a redundacy? since i had the columns in the Dim_Product, Dim_Category. And what should be the best way to design this, sorry im new in SSAS, so a lot of stuff im not sure.

|||

It's easier to illustrate this with a sample Adventure Works query - note that the .MemberValue of [Promotion].[Min Quantity] and [Product].[Reorder Point] attributes is only meaningful at the appropriate granularity of those dimensions:

Code Snippet

With

Member [Measures].[WeightedQuantity] as

iif(IsEmpty([Measures].[Order Quantity]) or

[Promotion].[Min Quantity].MemberValue = 0,

Null,

([Measures].[Order Quantity] /

[Product].[Reorder Point].MemberValue)

* [Promotion].[Min Quantity].MemberValue),

FORMAT_STRING = '#,0.0'

select

{[Measures].[WeightedQuantity]} on 0,

Non Empty [Product].[Product].[Product]

* [Promotion].[Promotion].[Promotion] on 1

from [Adventure Works]