I have a requirement to show a report which calculates the average selling unit price of each product and then multiplies this average by the number of units forecasted to sell in the next year (for each product).
At this point my main issue is getting this set up so the totals roll up correctly when viewing at product category level. Using AdventureWorks as an example (and sales orders instead of forecasts) I've got this far...
with
member [Measures].[Sales Order Value]
as sum(descendants([Product].[Product Categories].currentMember,
[Product].[Product Categories].[Product]),
[Measures].[Average Unit Price] * [Measures].[Order Count]),
format_string = "Currency"
select ([Date].[Calendar].[Calendar Year].&[2008],
{[Measures].[Sales O开发者_StackOverflow中文版rder Value] }) on columns,
[Product].[Product Categories].[Subcategory].members on rows
from [Adventure Works]
I think this is about right, I believe this is going down to product level to apply the calculation between product average unit price and any sales orders for the product.
My issue is that I think the average unit price is being calculated over all data, I need to alter this to pick up an average based on the last 12 months only.
精彩评论