【DAX 系列】总计行问题终极解决方案

这是一个老生常谈的问题，本文将给你终极解决方案，并带您重新理解一种模式，从入门级到专业级，均有您需要的营养。

您可以直接使用本文的模式和套路立刻优化您的计算。

问题重现

如果你使用 DAX 很快就会发现总计行的问题。例如：

Topic.Problem =
// 这个度量值的总计行会有问题
[KPI] + 1000

出现错误的情况：

很容易看出总计行并不等于以上各行的和，然后大家会觉得 PowerBI 很傻，这个都不能算对。

其实大家误解 PowerBI 了。我们来仔细看看。

原因分析

对于业务用户会很自然地认为 总计 = 以上各行的和。

那再来看看这个案例：

这里的总计行就不等于以上各行的和，而且必须不等于。业务用户会说，嗯，应该不等于。

所以，问题来了：

你到底希望总计行等于以上各行的和还是不等于以上各行的和呢？

得到的答案往往是，该等于的时候等于，不该等于的时候就不等于。晕~~~

理解 PowerBI

PowerBI 进行这项计算的主要方式是通过 DAX，考虑到 通用性 和 一般性，DAX 的设计满足：

在可视化层的汇总表的每个单元格都在独立的环境计算。

这就是筛选上下文的根源。

但是，很多情况是 总计 = 以上各行的和。那么如何处理这个问题呢？

为了把这个问题揭露得更加彻底，让我们来彻底地吐槽出来先：

• 总计 = 以上各行全部的和
• 小计 = 在本区间内各行的和
• 如果用户做出了部分筛选，总计和小计的计算也应该是正确的

能同时满足上述三项的才是好方法。

问题分析

对于这个问题，我们此前就给出了解决方案，很多 DAX 玩家也使用了不同的方式。但今天我们给出更全的方式，而且是一个通用套路。

微软也已经发现了这个问题，就是在可视化层面目前没有给出一个计算方案。用英文的准确写法是：Visual Calculation。

DAX 的强大就在于灵活的上下文，尤其是筛选上下文。而出现的总计行问题也是由总计行引发的。

一般的思路是：

判断当前的计算环境，如果是总计行就计算全部，如果是小计行就计算当前部分，如果是元素行就默认计算。但问题是，如果用户在最外部筛选了部分元素呢。

以上这句加粗的如果您还看懂说明还没有掉入这个坑，这是为了您下次回看准备的。

问题的深化

如果认为问题这就结束了，就错了，还会出现四种不同的演化：

• 单列总计
• 多列总计，但属于同一个表
• 多列总计，且不属于同个表，但有共同的桥表
• 多列总计，彻底独立不同表

直接给答案

请仔细观察以下方案：

我晕，这是什么鬼~

左边的蓝色框反映了前 3 种情况，即：

• 单列总计
• 多列总计，但属于同一个表
• 多列总计，且不属于同个表，但有共同的桥表

右边的蓝色框反映了第 4 种情况，即：

• 多列总计，彻底独立不同表

值得注意的是，它们在外部的部分筛选下，还保证了计算的正确性。

下面分别说明。

在继续阅读之前，我们先给出一个套路，然后按照这个套路来看实现，最后再总结这个套路。

总计行通用套路

处理总计行计算，需要分二步：

• 第一步，定义元素计算，例如命名为： [KPI.Row]；
• 第二步，定义兼容计算，例如命名为： [KPI.Display]，该度量值放置后可确保元素行，小计行，总计行全部计算正确，且在部分筛选下依然正确。

其实，平时我们使用的度量值是[KPI.Row]，之所以出现总计行问题，也是因为没有考虑总计行问题。

在第二步中谈到的兼容计算，就是为了兼容元素，小计，总计，外部筛选等苛刻条件，保持极高的用户友好。

这里涉及一个关键函数是：SUMX。

套路：用 SUMX 按分组对 [KPI.Row] 进行包裹。即可。

下面从具体的案例来说明这个套路。

【情况1】单列总计

度量值：

Topic.Value.Display.FromOneTable.OneColumn =
SUMX( VALUES( Model_Product[类别] ) , [Topic.Value.Row] )

Topic.Value.Display.FromOneTable.OneColumn 只是个名字，不必在意。

效果：

请体会这个套路。

【情况2】多列总计，同表

这种情况比上面的情况更加复杂，因为可能是多列，但他们都来自同一个表。

度量值：

Topic.Value.Display.FromOneTable.MutiColumns =
// 多列总计，但多列来自同一个表
SUMX(
    SUMMARIZE(
        Model_Product ,
        Model_Product[类别] ,
        Model_Product[子类别]
    ) ,
    [Topic.Value.Row]
)

效果：

请体会这个套路。

【情况3】多列总计，不同表，同桥

这种情况更加复杂，多列来自不同的表，当然，有个特点是他们有一个共同的桥。

什么是桥表？ 在多个一对多关系中，作为共享的多端的表，就是桥表。 从连接的角度，叫做桥表；从数据仓库的角度，叫做事实表；从业务人员的角度，叫做明细表。

度量值：

Topic.Value.Display.FromMultiTable.ButSameBirdge =
// 多列总计，多列来自不同的表，但共享一个桥表
// 什么是桥表，在多个一对多关系中，作为共享的多端的表，就是桥表。
// 从连接的角度，叫做桥表；从数据仓库的角度，叫做事实表；从业务人员的角度，叫做明细表。
SUMX(
    SUMMARIZE(
        Model_Order ,
        Model_Product[类别] ,
        Model_User[细分]
    ) ,
    [Topic.Value.Row]
)

效果：

值得说明的是，产品[类别]和客户[分类]来自不同的表，但都都与订单表相连。如下：

其中的核心 DAX 技巧在于：

SUMMARIZE( Model_Order , Model_Product[类别] , Model_User[细分] )

用桥表用做 SUMMARIZE 的第一个参数，与桥表连接的表的列都可以用作后续参数，则这个 DAX 表达式会返回合理的多列分组。

值得一提的是，这里的 产品[类别] 和 客户[分类] 是一个多对多的复杂关系，但也被轻松化解了。完全一模一样的套路。

【情况4】多列总计，不同表，无桥

这种情况更加更加复杂，多列来自不同的表，而且没有共同的桥表。如下：

度量值：

Topic.Value.Display.FromMultiTable.WithoutBirdge =
// 多列总计，多列来自不同的表，且没有桥表可以连接
SUMX(
    CROSSJOIN(
        VALUES( 'Data.A'[Item] ) ,
        VALUES( 'Data.B'[Item] )
    ) ,
    [Topic.X.Row]
)

效果：

大家可以自己验证如上的计算，非常简单。但它们是两个完全不同的表。

这里的技巧在于使用 CROSSJOIN 将分组进行了穷举组合。

对套路的解释

这里的本质在于：

现有一个元素行计算，设为 [KPI.Row]，令

KPI.Display =
SUMX (
    分组 ,
    [KPI.Row]
)

上述度量值无法运行，只是一个思路说明。

SUMX 包裹的 分组 将在筛选上下文的影响下发生巧妙的特性：

• 如果位于元素行，分组的结果就是当前元素，对自己算 [KPI.Row] 就是元素行。
• 如果位于小计行，分组的结果就是当前部分分组，SUMX 迭代之，分别计算元素行后加总，得到小计。
• 如果位于总计行，分组的结果就是所有可能分组，SUMX 迭代之，分别计算元素行后加总，得到总计。
• 如果外部有筛选上下文，可以在外部筛选下正确进行。

总结

通过本文我们彻底给出了一以贯之的 DAX 计算套路，它实现了如下特点：

• 可用于元素行计算
• 可用于小计行计算
• 可用于总计行计算
• 满足外部所有筛选有效
• 可以应对单列分组
• 可以应对多列分组，同表
• 可以应对多列分组，不同表，有桥
• 可以应对多列分组，不同表，无桥
• DAX 公式异常简单好记

同时满足上述 9 大特性，因此，我们说这是总计行问题的终极解决方案。以上内容 DAX 公式可以立刻套用到您的实际项目中来解决问题。

本文内容【源文件+视频讲解】从属于：年度订阅会员，已发布请享用。