如何正确计算测量值以进行汇总？

Question

我创建了一个计算指标，用于计算属于各种风险评级的所有贷款的当前余额的加权比率：

WITH
MEMBER [MEASURES].[Weighted Ratio] AS
    (Measures.[Current Balance] * [Risk Rating].[Loan Rating Code].CurrentMember.member_value) / 
    (
      [Measures].[Current Balance],
         Ancestor([Risk Rating].[Loan Rating Code].CurrentMember, 1)
    ), FORMAT_STRING = "Standard"
SELECT
    {[Measures].[Current Balance], [Measures].[Weighted Ratio]} on COLUMNS,
    [Risk Rating].[Loan Rating Code].MEMBERS ON ROWS
FROM
    [Test Cube]

问题是结果仅在儿童级别返回，而不是求和：

     Current Balance     Weighted Ratio
All  $3,245,749,329.46   (null)
0   $661,388.20          0.00
1   $17,341,801.10       0.01
2   $117,188,374.06      0.07
3   $596,477,812.37      0.55
4   $1,266,047,841.51    1.56
5   $986,789,425.72      1.52
6   $119,055,732.16      0.22
7   $133,050,242.69      0.29
8   $9,136,711.65        0.02

我怎样才能得到sum 显示在所有级别而不是 (null)？

Answer 1

看来您在编写

Ancestor([Risk Rating].[Loan Rating Code].CurrentMember, 1)

上面的函数“当涉及到根（所有级别）时尝试找到其祖先”时没有考虑根成员，这可能就是您在该级别得到 null 的原因。您应该决定当涉及[风险评级].[贷款评级代码]维度的成员“所有级别”时该怎么做。

看起来，当涉及到根级别时，您只需要返回一个常量值“1”即可。如果是这样，您应该更改您的查询，如下所示；

WITH
MEMBER [MEASURES].[Weighted Ratio] AS
( Case

        When [Risk Rating].[Loan Rating Code].CurrentMember.Level.Ordinal = 0
        Then 1
        Else (Measures.[Current Balance] * [Risk Rating].[Loan Rating Code].CurrentMember.member_value) / 
        (
             [Measures].[Current Balance],
             Ancestor([Risk Rating].[Loan Rating Code].CurrentMember, 1)
        )
  End
), FORMAT_STRING = "Standard"
SELECT
    {[Measures].[Current Balance], [Measures].[Weighted Ratio]} on COLUMNS,
    [Risk Rating].[Loan Rating Code].MEMBERS ON ROWS
FROM
    [Test Cube]

如果您想在根级别返回另一个结果，只需更改 Then 部分的逻辑即可。

Answer 2

我能够在我的计算度量中使用 IsLeaf() 和递归来使其工作：

WITH
MEMBER [Measures].[Weighted Ratio] AS
IIF(
    IsLeaf([Risk Rating].[Loan Rating Code].CurrentMember),
    ([Measures].[Current Balance] * [Risk Rating].[Loan Rating Code].CurrentMember.member_value) / 
    ([Risk Rating].[Loan Rating Code].[All], [Measures].[Current Balance]),
    SUM([Risk Rating].[Loan Rating Code].CurrentMember.Children, [Measures].[Weighted Ratio])
), FORMAT_STRING = "Standard"
SELECT
    {[Measures].[Current Balance], [Measures].[Weighted Ratio]} on COLUMNS,
    [Risk Rating].[Loan Rating Code].MEMBERS ON ROWS
FROM
    [Test Cube]

现在结果看起来像我想要的：

     Current Balance     Weighted Ratio
All  $3,245,749,329.46    4.24
0    $661,388.20          0.00
1    $17,341,801.10       0.01
2    $117,188,374.06      0.07
3    $596,477,812.37      0.55
4    $1,266,047,841.51    1.56
5    $986,789,425.72      1.52
6    $119,055,732.16      0.22
7    $133,050,242.69      0.29
8    $9,136,711.65        0.02