MDX – Child Aware Calculations

Any business domain reporting solutions has a requirement to analysis its measures depending on its KPI thresholds. It is very common to have business entities hierarchy involvement in this analysis.
Here discussing one possible common mistake and easy approach to solve this using a simple MDX calculation using AdventureWorks Cube. Scenario is to get the products category/sub-category threshold message depending on its product sales to a threshold level.

WITH 
// Get the Hierarchy Level of the Current member
MEMBER [Measures].[ProductLevel] AS
[Product].[Product Categories].CurrentMember.Level.Ordinal

MEMBER [Measures].[KPIStatusMessage] AS
// KPIStatusMessage message depending on threshold level value
IIF( [Measures].[Reseller Sales Amount] < 15000 , "Failure" , "Success" )

MEMBER [Measures].[KPIMissingTargetChildsCount] AS
// KPIMissingTargetChildsCount child members missing Threshold value
COUNT(
    FILTER(       
       nonempty(
       
               DESCENDANTS ([Product].[Product Categories].CurrentMember ,      [Product].[Product Categories].[Product])
             , [Measures].[Reseller Sales Amount]
                )                
           , [Measures].[Reseller Sales Amount] < 15000
          )
     )

MEMBER [Measures].[KPIStatusMessageFromChilds] AS
// KPIStatusMessageFromChilds message depending on childrens threshold level value
IIF(
 
  COUNT(
    FILTER(
       
       nonempty(
       
               DESCENDANTS ([Product].[Product Categories].CurrentMember , [Product].[Product Categories].[Product])
             , [Measures].[Reseller Sales Amount]
                )
                
           , [Measures].[Reseller Sales Amount] < 15000
           )
        ) 
        > 0 
        
        , "Failure" 
        , "Success" 
     )
     , fore_color = iif([Measures].[KPIMissingTargetChildsCount] > 1 , 255 ,0)

SELECT {[Measures].[Reseller Sales Amount]
      , [Measures].[ProductLevel]
      , [Measures].[KPIStatusMessage]
      , [Measures].[KPIMissingTargetChildsCount]
      , [Measures].[KPIStatusMessageFromChilds]} ON COLUMNS
, nonempty({[Product].[Product Categories].Members - [Product].[Product Categories].[All] }
          ,[Measures].[Reseller Sales Amount]
          ) ON ROWS
FROM 

( SELECT {
         [Product].[Product Categories].[Category].&[4] -- Accessories
         
         } ON COLUMNS
  FROM [Adventure Works]
)
CELL PROPERTIES 
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR],[BACK_COLOR]