# MDX Calculate per Price Range • ### 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

• 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 Monday, February 6, 2012 8:25 AM
• Marked as answer by 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

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 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
where [Date].[Calendar].[Date].&
```

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
where [Date].[Calendar].[Date].&```

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 Monday, February 6, 2012 8:25 AM
• Marked as answer by Wednesday, March 7, 2012 9:00 AM
Monday, February 6, 2012 8:24 AM