Hi all,



I would appreciate a hint on this probably pretty simple issue:



I have a calculated measure [Riscfactor] that is the minimum of measure
[Internal Risc] and measure [External Risk]. (both expressed as numeric
values, not normalized because they already include weights.)



[Riscfactor] is calculated on "Store"-Level and "Organization"- Level, with
store and organization building a hierarchie. (Each store belonging to one
organization)



On store level: [Riscfactor] = Max([Internal Risc], [External Risc])

On organization level [Riscfactor] = Sum of Riscfactors on store level



I used the following code:



CREATE MEMBER CURRENTCUBE.[MEASURES].[Riscfactor]

AS CASE

WHEN [OrgHierarchie].CURRENTMEMBER.LEVEL.NAME = "Store"

THEN IIF(

([Measures].[Internal Risc] < [Measures].[External Risc]),

[Measures].[External Risc]),

[Measures].[Internal Risc]

)

ELSE

SUM([OrgHierarchie].CURRENTMEMBER.Children,[MEASURES[Riscfactor])



That works fine, as long as I don't filter/slice on stores (e.g. in OWC or
Excel)



If I filter the resultset e.g. to display only two stores, the total for the
organization still is the total of all stores.

(Which actually is what I defined, by summing up all children of the current
member.)

Anyway, I am looking for a solution, that limits the total to only those
children, that are selected. (Something like CURRENTMEMBER.CURRENTChildren)



Any help or ideas will be very much appreciated



John

You can use Filter with existing within sum function for example, i guest.

Regards,
Mariana.

"bjh" <nospam (AT) webtelligence (DOT) net> wrote