SSAS MDX计算度量问题：总计（Grand Total）无法更新

地理层次结构片段 #

原计算度量MDX脚本 #

CREATE MEMBER CURRENTCUBE.[Measures].[Metric - vs PY %]
AS
IIf([Geography].[Geography Hierarchy].LEVEL.ORDINAL <> 5,
sum(existing [Geography].[Geography Hierarchy].[1 Country].members
, case when isempty(([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value]))
or isempty(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
or isempty(([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value]))
then null
else
(([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value])
/ ([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value])
- 1)
* ([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value])
end
)
/
sum(existing [Geography].[Geography Hierarchy].[1 Country].members
, case when isempty(([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value]))
or isempty(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
or isempty(([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value]))
then null
else
([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value])
end
)
, case when isempty([Measures].[Base_Metric])
or isempty(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
then null
else
DIVIDE([Measures].[Base_Metric]
, ([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))-1
end
)

问题原因与修正方案 #

核心问题 #

1. 总计行（Grand Total）的层级序数为-1，不属于脚本中判断的<>5分支，也未匹配第5级逻辑，导致计算逻辑缺失。
2. 原脚本中SUM仅针对[1 Country].members计算，总计行上下文无对应成员，求和结果异常。

修正后的MDX脚本 #

CREATE MEMBER CURRENTCUBE.[Measures].[Metric - vs PY %]
AS
// 适配总计行（层级序数=-1）和非第5级的逻辑
IIf([Geography].[Geography Hierarchy].LEVEL.ORDINAL = -1 OR [Geography].[Geography Hierarchy].LEVEL.ORDINAL <> 5,
    SUM(EXISTING [Geography].[Geography Hierarchy].Members,
        CASE 
            WHEN ISEMPTY(([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value]))
                OR ISEMPTY(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
                OR ISEMPTY(([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value]))
            THEN NULL
            ELSE
                (([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value])
                / ([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]) - 1)
                * ([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value])
        END
    )
    /
    SUM(EXISTING [Geography].[Geography Hierarchy].Members,
        CASE 
            WHEN ISEMPTY(([Metric Attributes].[Metric name].&[64724],[Measures].[Metric Value]))
                OR ISEMPTY(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
                OR ISEMPTY(([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value]))
            THEN NULL
            ELSE ([Metric Attributes].[Metric name].&[64150],[Measures].[Metric Value])
        END
    ),
    // 第5级原有逻辑保留
    CASE 
        WHEN ISEMPTY([Measures].[Base_Metric])
            OR ISEMPTY(([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value]))
        THEN NULL
        ELSE DIVIDE([Measures].[Base_Metric], ([Metric Attributes].[Metric name].&[64724],[Measures].[Prev Period Metric Value])) - 1
    END
),
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = [Measures].[Metric Value],
VISIBLE = 1;

优化说明 #

• 新增总计行判断逻辑，让总计行使用加权平均方式计算百分比。
• 扩展SUM的成员范围为整个地理层次结构成员，确保上下文筛选正确。
• 添加格式和非空行为属性，提升度量显示效果和计算效率。

内容的提问来源于stack exchange，提问作者Sudhir Upadhyay