none
Sum Average values RRS feed

  • Question

  • I have a measure which does average basically using Sum/Count in MDX Calculation.   What i want is to add the Average value at Total level.

    Meaning at Total level of each group it shoul sum all the average values , So at each cell it would still be average , but at the total level it should be summed.

    I know this can be done using scope , but really don't know how.

     

     

    Friday, January 22, 2010 9:42 PM

All replies

  • How many dimensions are related to the measure group contiaining the Sum and Count measures - and is this behavior only desired for a specific dimension? Could you explain your requirment using the [Average Sales Amount] measure in Adventure Works as an example?


    - Deepak
    Monday, January 25, 2010 3:15 AM
    Moderator
  • They are about 5 Dimension attached to measure group , all the fact columns  are with aggregation type sum and have one count measure generated by SSAS.  This is not really specific to any dimension , this is global, this is be sliced witha any dimension.


    The requirment is basically i have a dimension called Aggregation category , So based on the Aggregation category selected , I want the engine to calculate based on aggregation type selected,

    i have scope statment which sums  or average.

    So when the Aggregation category is sum , it sum all the measure group , which is nothing but default. When i select the Average the aggregation average all the sum values.

    To derive this i am using a scope statment.
    Average
    SCOPE({MeasureGroupMeasures("Internet Sales")}
    -{[Measures].[Internet Transaction Count]
    }); 
    ([Product].[Category].&[3])
    = [Measures].CurrentMember/[Measures].[Internet Transaction Count];
    END SCOPE;

    But now what i need is the average which does average at cell level should be summed at total level. I tried using Descendants and After function but that seems to throw me a error , I am not even sure if the approach i am taking works

     This approach was used for date time calculation, then i would proably try doing this for each dimension.

    SCOPE({MeasureGroupMeasures("Internet Sales")}
    -{[Measures].[Internet Transaction Count]
    }, Descendants([Date].[Date],,After),[Product].[Category].&[3]); 
    This = Sum([Date].[Date].[Date], [Measures].CurrentMember);
    END SCOPE;

     

     

     

     













     

    Monday, January 25, 2010 4:14 PM
  • I have a fact that works fine with average , I want to calculated weighted average. I have this calculation , apparently it performs bad after deploying it.  The intention is to use weighted average globally based on a dimension member type.


    any correction in this wil be helpful

    I was trying to take the approach from Martin Mason forum on
    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3f8173c0-1962-4405-b6e9-edb0c335761c/


     

     

     

    SCOPE({MeasureGroupMeasures("Internet Sales")}
    
    -{[Measures].[Internet Transaction Count]}); 
    
    ([Product].[Category].&[3])
    
    = Sum(existing [Date].[Date].[Date],[Measures].CurrentMember*[Measures].[Internet Transaction Count])/[Measures].[Internet Transaction Count];
    
    END SCOPE;

     

    • Edited by Qute Wednesday, January 27, 2010 3:17 AM need more attention
    • Merged by Raymond-LeeModerator Saturday, February 6, 2010 4:03 AM The same issue
    Tuesday, January 26, 2010 12:20 AM
  • I want to basically calculate the weighted average for a particular member type.  but that should be application to the scope of whole measure group.

    please advise any suggestions.  i have the concept of average that works fine. , but i need to get the weighted average working.

    Any suggestions would be helpful
    Wednesday, January 27, 2010 3:19 AM
  • Hi,

    For this kind of issue, you can try to achieve that in Script level, another similar thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/f14c93e5-edc3-4cbf-b63a-df7cb0ca08a0

     

    Regards,

    Raymond

    Thursday, January 28, 2010 2:27 AM
    Moderator
  • The problem i have is i have quite a few measures in cube i mean in hundreds , and this applies to all measures and is not specific to any measure. applying the suggestion in the post mention may not be little practical.


    when you mentioned about acheving it using script level , could you please provide more details about it and how can this be achieved.

    please advise

    thank you

    Thursday, January 28, 2010 2:44 AM
  • The problem i have is i have quite a few measures in cube i mean in hundreds , and this applies to all measures and is not specific to any measure.

    Did you try the statement I posted in another thread? You can modify the statements as you posted

    scope(MeasureGroupMeasures("Reseller Sales")

    -{[Measures].[Discount Amount]});

    [Product].[Category].[Category]=[Measures].currentmember/[Measures].[Order Count]; //it will change the total level.
    end scope;

    But you cannot do this for each dimensions and attributes. If you try to scope the each key attribute of the related dimension, then you will find:

    1)    There is no change for your measure value, because scope for each attributes just like using measure expression and named column. It will try to calculated from the lowest granularity, but each count measure on the lowest granularity is 1 (one), right? So the total member is:

    sum([Measures].[A]/1) = sum([Measures].[A]).

     

    Just like create another named column in you fact table [value]/1, and then sum of that value.

    2)    The [all] member in SSAS is special, it is not the way like this: sum(member.children), it is something like this: get the set of the all member and then apply for calculation. The reason is each [all] member is a not just the total, it’s also a member of another attribute. For example. the total of month is [all], but it’s also a member of [year], so if you scope the month level, then you will find year level will be unreasonable.

     

     

    Even if you can create your total member manually, like this:

    with member [Product].[Category].test1 as

    sum(existing [Product].[Category].[Category])

    But you still need to create the total member for every possible attribute. So you have to repeat the above steps (scope statements) for the related attributes. Regarding scope statement, you may take a look at this thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d108488f-ac5d-46aa-ad0c-e9e31166da48

     

    Regards,

    Raymond

     
    Thursday, January 28, 2010 4:59 AM
    Moderator