Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Use Scope to calculate Average at various dimensions

Proposed Answer Use Scope to calculate Average at various dimensions

  • Monday, January 28, 2013 4:50 PM
     
     
    Sign In to Vote
    0

    All,

    I have a cube that has a price point dimension, solution dimension, solution cateogry dimension and fact table that has list price. 

    Within the price point dimension I have quote_nbr attribute and a hierarchy called PricePts defined with one level Product Grouping.

    Solution dimension has Financial Hierarchy with levels LOB > Offer Group > Adv Solution

    Currently the cube is summing up list price.

    I need it to average the list price based on how many product groupings there are and need it to do this regardless of where I am  in the cube.

    For example:

    quote nbr        product grouping      list price

    111111            selfserver 22           125.23

                           selfserver 25           150.22

                           selfserver 26            160.00

    What I want is whenever I don't have product grouping listed the list price to be an average.  So I should see

    quote nbr      list price

    1111111        125.23+150.22+160.00/3

    I have been trying to do this with scope but am having problems

    MS

     

All Replies

  • Monday, January 28, 2013 8:23 PM
    Answerer
     
     Proposed Answer
    Sign In to Vote
    0

    you would usually create 2 physical measures, one with SUM and one with COUNT as AggregationFunction

    then you would just divide SUM by COUNT in a calculated measure to get the average

    this works for all scenarios

    hth,
    gerhard

    - www.pmOne.com -