The Product Hierarchy consists of 3 levels: Category, SubCategory and Product
I have firstly created a dynamic set [Selected Products]:
CREATE DYNAMIC SET [Selected Products] as (
EXISTING [Product].[Product].members - [Product].[Product].[All]
);
I have a measure_ that is calculated as follows:
CREATE MEMBER CURRENTCUBE.[MEASURES].[MyMeasure(%)_]
AS IIF([Measures].[Total] <> 0, [Measures].[SomeMeasure]/[Measures].[Total], NULL)
and it is used to calculate the following measure:
CREATE MEMBER CURRENTCUBE.[MEASURES].[MyMeasure(%)]
AS
(
IIF(
[Product].[Product Hierarchy].CurrentMember IS [Product].[Product Hierarchy].[All]
,AVG([Selected Products],[MEASURES].[MyMeasure(%)_]),
AVG(Descendants([Product].[Product Hierarchy].currentmember,[Product].[Product Hierarchy].Levels('Product'),SELF)
, [MEASURES].[MyMeasure(%)_])
)
)
Grand total calclation is fine as it takes all the [Selected Products] Calculation on Product level is also fine.
The problem is when it comes to calculate on Category or SubCategory level when not all belonging products are selected. The returned values in fact are calculations for all belonging children and not selected only.
Is is possible to amend above Descendants() expression so it refers to [Selected Products] only?
How can I re write?