January 8, 2004 at 3:15 am
Hello,
I've created a sales Cube with measures salesPrice that is the complete price (Unit Price * Quantity) And Quantity.
Now I want to calculate the Unit Price.
Pretty easy you think. BUT the calculation has to be done on the lowest level of the products dimension (item Number).
The price range of products is big. There a products witch cost 1 mio. And products that cost 10 €.
And if you do the calculation on a higher level you get incorrect results.
example :
Sales Quantity Unit Price
1.000.000 2 500.000
1.000 10 1.000
-------------------------------------
1.001.000 12 83.417 and the summary should be 501.000
Can anyone help me?
Greetings,
Nico
January 8, 2004 at 12:37 pm
I believe your assumption that [Unit Price] should be additive is incorrect, so the result of 83,417 should be the right one. Since both [Sales Price] and [Quantity] are additive, the proper result is simply derived by taking the ratio of the aggregates over the slice in question, such as [Unit Price] = Sum([Sales Price]) / Sum([Quantity]). Thus the [Unit Price] behaves like an Average.
If you have the standard sample Cubes that come with Analysis Services, run this query against the Sales Cube in the SalesMart 2000 database:
WITH
MEMBER [Measures].[Average Unit Price] AS '[Measures].[Store Sales] / [Measures].[Unit Sales]'
SELECT
NON EMPTY { [Measures].[Average Unit Price], [Measures].[Unit Sales] , [Measures].[Store Sales] } ON Columns,
NON EMPTY CrossJoin({[Store].[Store State].[CA], Descendants([Store].[Store State].[CA], [Store].[Store Name]) } , { [Product].[All Products], [Product].[Product Family].Members } ) ON Rows
FROM Sales
I am only selecting results for the state of CA at the [All Products] and [Product Family] levels. Observe that the [Average Unit Price] behaves like an average and is analogous to your situation.
BTW - I think you have a typo in your message. The second tuple (1000, 10, 1000) should have the value 100 for Unit Price and not 1000, or else the other values are inconsistent.
- Paul
January 13, 2004 at 12:00 am
Still my question standes. Because I don't want the average price. I need the unit price at lowest level. And it will be aggregated if we look at data at a higher level. This result is a part of bigger mathematical formula. greetz, Nico
January 13, 2004 at 6:34 pm
Nico,
Is your source data in a format similar to:
date|product|Qty|SalesPrice
If so, why not do the derivation of unitprice at the view/table level, making the format:
date|product|Qty|SalesPrice|UnitPrice
where UnitPrice = SalesPrice / Qty .
This should then make UnitPrice a fully additive measure.
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply