最近阅读“the Definitive Guide to DAX”英文原著（中文译本《DAX权威指南》），第四章中讲解了一个“客户平均年龄”的典型案例，借此介绍了SUMMARIZE函数。在Tableau中，我们可以用LOD函数完成相同的计算。
Tableau 的LOD表达式，可以与SQL的“嵌套聚合查询”相对应，它首先是嵌套查询（nested query），其实还必须是聚合查询（aggregate query）。因此，在Power BI中，类似的案例就需要使用Summarize、AddColumns或SummarizeColumns等具有分组聚合功能的函数才能实现。
“多维分析”是业务分析的经典、高级场景，而Tableau和Power BI作为商务智能领域的“BI双擘”，可以说走向了明显不同的方向——喜乐君在之前总结了“Power BI向左，Tableau向右”。为了帮助更多人理解二者处理“多个详细级别问题”时差异，本文结合书中的案例整理如下。
- 问题分析
- 方案1：Power BI Summarize函数
- 方案2:Tableau Include LOD表达式
新用户慎入，欢迎大家留言。
一、问题解析：问题结构与业务背景
基于超市销售的交易数据明细（订单ID*产品ID），分析指标“客户平均年龄”（the average age of customers）。
完整的问题是：每个产品类别 的 客户平均消费年龄
按照喜乐君在《数据可视化分析》中介绍的问题解析方法，这里维度是“的”左侧的“产品类别”，度量是“的”字右侧的“客户平均购买年龄”，这里没有筛选范围。
分析的本质是聚合，在这个问题中，“平均年龄”仅需要对客户的年龄做平均值聚合（average）。但是，“平均值”其实隐含了“数量”这一条件，只有在一个客户、一个年龄仅仅购买过一次时，这种直接的聚合才能解决问题，否则一个客户一个月中的多次购买，就会被多次聚合，相当于为它的年龄增加了权重（来的次数越多，对“平均年龄”的影响越大）。书中的解释如下：
Compute the average age of customers at the time of sale, counting each customer only once if they made multiple purchases at the same age .
the Definitive Guide to DAX
为了解决这个问题，技术上有两个方案：
- 要么预先把交易明细表聚合到足够简单的详细级别（比如，预先生成“每个客户、每个年龄的销售总和、订单次数”这样的聚合表），用事先准备的数据表再次平均完成上述题目。这是缺乏高级计算的BI工具的方案，相当于用ETL的方式弥补高级计算的不足。如果用SQL，也可以把这个预先聚合过程保存为view视图，本质也是ETL。
- 要么，在分析过程中，借助于高级计算创建临时的聚合表，然后二次聚合。在SQL中，可以用“嵌套聚合查询”完成这个过程，在Power Bi中，可以使用Summarize函数创建临时数据表（表函数），它们都是“query table from table”的思路，对于业务用户较难理解。在Tableau中，可以使用LOD表达式完成“指定详细级别的预先聚合”；
可见，这个问题的难点是，如何通过高级计算消除重复；或者说，在最终的问题聚合之前，还隐藏包含了一个预先的聚合。
接下来的案例结束中，Power BI数据中采用color字段作为维度，Tableau超市数据中使用子类别字段作为维度，从而完成“各个产品类别的客户平均消费年龄”。
读者可以使用SQL完成类似的操作，欢迎好评，喜乐君将选择最佳答案，赠送图书一本。
二、DAX的Summarize临时聚合表方案
Power BI中，计算分为“计算列”和“度量值”，前者可以理解为数据表明细级别的数据准备，后者可以理解为围绕问题的聚合。在这个题目中，我们先要计算每个客户在每次消费时的当下年龄，这个计算是和最终问题无关的、是在数据表明细行中有意义的、可以对应到具体业务过程的计算，因此要用计算列（calculated column）完成。
在PBI中，先使用计算列计算“每个客户消费时的当下年龄”。它等于出生日期和订单日期的年数间隔。如果销售交易数据表中没有出生日期（通常是没有的），还需要可以使用RELATE函数从其他数据表中引用。如下所示：
SALES[customer Age] =
DATEDIFF( --compute the difference between 计算如下的日期差异：
RELATED(Customer[birth Date] ), -- the customer's birth date 客户出生日期
Sales[Order Date], -- and the order date 和订单日期
YEAR -- in year 查看间隔年度
)从功能上看，“计算列”的结果字段，接下来可以等价于数据表字段，既可以作为维度，又可以被聚合成为度量。 很多初学者会尝试直接用用如下的计算作为最终问题的度量：
-- 错误方案1
Avg Customer Age Wrong :=
AVERAGE (SALES[customer Age] )不过，直接计算上述字段的平均是错误的，它会重复计算一个客户在同一时间的多次购买——一个人会被计算很多遍。这个前面已经介绍。
官方还介绍了一种错误的做法：使用DISTINCT函数，返回年龄的不重复值，而后计算平均。
-- 错误方案2
Avg Customer Age Wrong :=
AVERAGEX ( -- iterate on the distinct values of customer age 客户年龄平均值
DISTINCT (Sales[customer Age]), -- 返回客户年龄的唯一数据集
SALES[customer Age]
)相比上一种错误的做法，它会把相同年龄的多个客户作为一个值计算，相比上一种错误做法（客户年龄被多次计算），这里的数据就会太少（相同年龄的客户会被忽视）。
比如，喜乐君和小王同年同月出生（年龄一样18岁），并在同一个时间多次购买了同一个产品类别（比如家具）的产品，那么第一种直接平均的方法中，喜乐君和小王都会被计算多遍；而在第二种DISTINCT的计算中，喜乐君和小王两个人会在“家具”分类下，仅仅出现一个18被平均。可见，前者太多，后者太少；或者说，前者的计算过于具体，后者的计算过于抽象，以至于失真。
正确的方案是，先使用“DAX表函数”消除重复，预先完成“每个客户、不同年龄的交叉表”，如下使用了SUMMARIZE函数；而后在上述临时聚合表基础上，再次聚合平均值。如下所示：
-- 书中的正确方法
Avg Customer Age correct:=
AVERAGEX ( -- iterate on 迭代：
SUMMARIZE( --all the existing combination 针对如下所有组合的数据表：
Sales, -- that exist in Sales 在sales明细表中，
Sales[customer ID], -- of the customer id and 每一个客户ID 和
Sales[customer Age], -- the customer age 客户年龄
)
SALES[customer Age] ) -- and average the customer's age 计算客户年龄的均值
)注意，这里SUMMARIZE的变量是数据表和维度字段（客户ID和客户年龄——来自calculated column）。 而当视图中行字段是Color时，行字段也会影响上述的聚合——因此，一个人在不同类别有购买，年龄就会在不同类别下分别计算。用DAX的话说，视图的维度也是DAX度量值计算的“上下文/背景”（filter context），上述Summarize中已经预先包含了客户ID和客户年龄两个维度，此时再引用视图中的分类字段，就变成了三个维度字段的预先聚合。
喜乐君把这个过程划分为三个步骤，其中最难理解的是第三步，涉及到“上下文转换”“嵌套”等多个专业知识。
- 第一步：计算每一行中，客户的消费年龄 （计算列）
- 第二步：生成临时表：维度是产品类别（color）*客户ID*客户年龄 （表函数）
- 第三步：在上述临时表基础上，以产品类别（color）为依据，计算客户年龄均值（忽略客户ID） （上下文转换）
如下图所示，介绍了原书中的说明。
相信很多IT用户看到这里也是一头雾水，业务用户则鲜有半小时内能完整理解题目的DAX实现的——至少喜乐君本人就花了半个小时以上的时间。
相比之下，如果改用SQL的嵌套查询或者view视图，这个问题会更加简单；而使用Tableau的LOD表达式，“表函数”降维变成了更易于理解的“字段函数”，所以更容易被业务用户理解和接受。
三、LOD表达式：Tableau 的解答方法
如果是Tableau中，这个问题的过程也是一样的，首先行级别计算计算订单消费时的客户年龄；其次聚焦客户年龄，避免多次计算。
- 第1步，使用datediff计算客户消费时的年龄 :
- 第2步，创建视图（这里使用子类别代替上面的color）。
这里的难点也是计算。
年龄直接平均显然是不对的（一个客户同一年的多次购买会全部参与平均值聚合）（图中第一列）。
如果指定“每个子类别、每个客户”呢，假设喜乐君在20岁买过3次打印机，小王在18岁买过4次打印机此时的计算，指定子类别、客户ID计算年龄均值时，喜乐君和小王会被分别聚合3次和4次——购买频次变成了权重，这不是我们这里想要的（下图第二列）。
正确的逻辑是，先获得“同一个客户同一年龄中的购买”问题，即指定子类别、客户、年龄返回唯一值——这里使用AVG或者MIN都可以——目的仅仅在于去重。假设喜乐君在20岁买过3次打印机，小王在18岁买过4次打印机，在计算“打印机的客户平均消费年龄”时，喜乐君和小王分别都被计算一次，不多也不少。既要去重，又不能进一步简化到无意义。
考虑到LOD的结果还要二次聚合，使用AVG或者MIN返回去重的年龄。所以正确的计算是：
AVG( {FIXED [Sub-Category],[Customer Name],[消费年龄]: AVG([消费年龄])})基于这样的计算，一个客户、同一年龄的多次购买会被简化为一个值；而一个客户、多个年龄的购买会被保留不重复的多个值。不同客户之间遵循相同的逻辑，最终获得聚合值。
在Tableau中，这里的子类别正是视图维度，因此可以用INCLUDE代替，如下：
AVG( {INCLUDE [Customer Name],[消费年龄]: avg([消费年龄])})和前面的DAX表达式类似，INCLUDE函数自动引用了视图的维度作为聚合的依据，而DAX中的SUMMARIZE自动引用了视图维度构建“临时表”的依据——这里的依据都是产品分类、客户、年龄三个维度字段。在Tableau中，只需要理解计算的优先级，不同LOD表达式的差异，而在DAX还要理解“上下文转换”等更复杂的技术细节。
这样一看，这一简单的一行，就相当于PBI中的冗长的一大段，这里重复一下，强烈对比：
-- 书中的正确方法
Avg Customer Age correct:=
AVERAGEX ( -- iterate on 迭代：
SUMMARIZE( --all the existing combination 针对如下所有组合的数据表：
Sales, -- that exist in Sales 在sales明细表中，
Sales[customer ID], -- of the customer id and 每一个客户ID 和
Sales[customer Age], -- the customer age 客户年龄
)
SALES[customer Age] ) -- and average the customer's age 计算客户年龄的均值
)四、小结
基于这样的对比，读者应该能理解不同工具背后，对相同问题的处理方式有相同之处，但是技术实现又有明显的差异。Tableau的LOD表达式必须是建立在聚合基础上的，而DAX的临时表则可以是没有聚合的“交叉表”——DISTINCT和VALUES函数就是最简单的数据表。
DAX高级的计算，足以把数据准备、临时表、聚合都完全置于代码中完成，甚至可以脱离可视化存在，这就是面向IT的思路——高度可定制，充分的灵活性，同时带来的是“专业性门槛”。
Tableau采用的“详细级别”思路，任何聚合必然是相对于特定详细级别的聚合，预先的聚合用LOD表达式实现——而且是基于字段逻辑理解，而非表逻辑，虽然也有专业难度，但相比DAX明显更容易被接受。获句话说，Tableau把复杂的“表逻辑”，转换为了“字段逻辑”。
读者可以在下面的文章中，了解两个工具更深层次的差异：如何选择BI工具：Power BI“向左”，Tableau“向右”
简洁与否，一目了然。
喜乐君
Nov 29, 2022 V1 关键案例
Apr 8,2023 V2 大幅度调整本文
了解 喜乐君 的更多信息
订阅后即可通过电子邮件收到最新文章。