locked
MDX Calculate per Price Range RRS feed

  • Question

  • Hi, its the newby again.

    I have a report in Excel that generates it's info from a SSAS cube. The client has asked me to add new calculations "sales Units per RSP range". My Productgroup hierarchy is : GenericGroup, Department, Class, Subclass. This calculation must be done on subclass level, but sum up to Class/Department/Generic Group.

    I've did an calculation, and it worked for subclass, but it does not sum. IIF(([Measures].[Ave RSP] < 200), SUM( [Measures].[Nett Sales Quantity]),NULL) I know I have to bring in the Product level in, but have no clue how.

    SUBCLASS

    STYLE

    Units

    Sales value

    Ave RSP

    Range <200

    Range 200 – 399

    Range 400+

    78010201

    780102019901

    488

    117342.65

    240.46

    0

    488

    0

     

    780102019990

    21809

    4318182.00

    198.00

    21809

    0

    0

     

    780102019997

    695

    346110.00

    498.00

    0

    0

    695

    78010201 Total

    22992

    4781634.65

    936.46

    21809

    488

    695

    Thursday, February 2, 2012 9:27 AM

Answers

  • Hi Helcas,

    I suppose the calculation of "sales units per RSP range" works fine at the subclass level. Then, for "but sum up to Class/Department/Generic Group", you can write scope assignment to do it. Here is an example for the Class level -

    SCOPE([Product].[Productgroup].[Class].members,[Measures].[Ave RSP]);

    This = SUM([Measures].[Ave RSP],Descendants([Product].[Productgroup].currentmember,[Product].[Productgroup].[Subclass]));

    End Scope;

    Based on the above scope statement, it is easy to write ones for Department/Generic Group levels. So, then "This calculation must be done on subclass level, but sum up to Class/Department/Generic Group".

    Regards,
    Jerry

    • Edited by Jerry Nee Monday, February 6, 2012 8:25 AM
    • Marked as answer by Jerry Nee Wednesday, March 7, 2012 9:00 AM
    Monday, February 6, 2012 8:24 AM

All replies

  • Hi Helcas

    Can you just let us know what exactly out Put you want, from My undersating you want to show you all the sum value on hierrachy levele i.e "Class/Department/Generic Group".

    Put below expression and check

    IIF(([Measures].[Ave RSP] < 200), SUM("Put Your Dimension hierrachy for Class/Department/Generic Group",[Measures].[Nett Sales Quantity]),NULL)

    Suhas | MCP | MCTS


    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Thursday, February 2, 2012 10:08 AM
  • In the original message, I copied the pivot table to show the effect I want. Style is the lowest level and if the Ave RSP @ Style level is < 200 it must be summed as such on the previous levels. Currently it takes the Ave RSP per level . As you can see on the pivot table , I have 3 styles in the Subclass. 1 falls under < 200 Ave RSP, 1 falls under 200-399 RSP and 1 falls under 400+ RSP. When I drill back to subclass level, I don't want to see sales per subclass level Ave RSP, I still want to see the total per style per range. In the total column, I therefore want to see the totals I hightligted. 21809, 488 & 695 and NOT 0, 0 & 22992 or nothing.
    • Edited by Helcas Thursday, February 2, 2012 1:22 PM
    Thursday, February 2, 2012 1:21 PM
  • Hi Helcas

    Im not totally sure I understand the issue in question, but If the problem is related to the fact you need to perform the calculation only at a specific level (subclass) and then rollup from there, then you can approach this by overriding the actual cell values in the cube.  For example, take a look at this query

    with 
    member [Measures].[X] as
    iif([Measures].[Internet Sales Amount] <= 300, [Measures].[Internet Sales Amount], null)
    
    select {[Measures].[Internet Sales Amount],[Measures].[X]} on 0,
    non empty 
    	  [Product].[Product Categories].Children 
    	* [Product].[Subcategory].Children 
    	on 1
    from [adventure works]
    where [Date].[Calendar].[Date].&[20080101]
    


    It returns all product categories and subcategores and the measures [X] shows that at the subcategory level there are 2 members that have an [internet sales amount] of less than 300 and that belong to the 'accessories' category.

    if you remove the [subcategory] level from the row axis, the numbers at the category level won't add up those two values identified above, as now the aggregation is performed only at the 'category' level.

    In order to override this behavior, you can use a SCOPE() statement in the cube mdx script, or a query scoped CELL CALCULATION, as in:

     

    with 
    cell calculation AggUnder300 for
    '([Measures].[Internet Sales Amount],  
    	Descendants([Product].[Product Categories].CurrentMember, [Product].[Product Categories].[Subcategory]))'
    as 'iif([Measures].[Internet Sales Amount] <= 300, [Measures].[Internet Sales Amount], null)'
    , format_string = "currency"
    
    select {[Measures].[Internet Sales Amount]} on 0,
    non empty 
    	  [Product].[Product Categories].Children 
    	//* [Product].[Subcategory].Children 
    	on 1
    from [adventure works]
    where [Date].[Calendar].[Date].&[20080101]


    Here you can see the calculation is assigned at the subcategory level even though only categories are shown on the rows axis; and the resulting output properly aggregates the two members identified at the subcategory level.

     

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    Thursday, February 2, 2012 3:11 PM
  • Hi Helcas,

    I suppose the calculation of "sales units per RSP range" works fine at the subclass level. Then, for "but sum up to Class/Department/Generic Group", you can write scope assignment to do it. Here is an example for the Class level -

    SCOPE([Product].[Productgroup].[Class].members,[Measures].[Ave RSP]);

    This = SUM([Measures].[Ave RSP],Descendants([Product].[Productgroup].currentmember,[Product].[Productgroup].[Subclass]));

    End Scope;

    Based on the above scope statement, it is easy to write ones for Department/Generic Group levels. So, then "This calculation must be done on subclass level, but sum up to Class/Department/Generic Group".

    Regards,
    Jerry

    • Edited by Jerry Nee Monday, February 6, 2012 8:25 AM
    • Marked as answer by Jerry Nee Wednesday, March 7, 2012 9:00 AM
    Monday, February 6, 2012 8:24 AM