开发者

MDX Calculating 12 month average unit price and applying to forecast quantity

开发者 https://www.devze.com 2023-02-16 15:38 出处:网络
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 e

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]

MDX Calculating 12 month average unit price and applying to forecast quantity

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消