【王者之战】Tableau LOD和Power BI DAX函数案例对比

Tableau Include和Power Bi summarize函数 最近阅读《the definitive guide to DAX》(中文《DAX圣经》),第四章中讲解了一个典型案例,借此介绍了SUMMARIZE函数,这里笔记整理如下,并借此说明PBI与Tableau在处理“多个详细级别问题”时的方法差异。

一个是强大的DAX,另一个是高级的LOD表达式。

1、问题解析

以超市数据为例,分析“客户平均年龄”(the average age of customers of Contoso)。由于一个客户可能在同一年度购买多个产品,重复购买多个产品,直接计算平均值,就相当于把频次作为了结果的权重——哪些同一个年度购买多次的客户,在结果中被计算了多次。

为了解决这个问题,我们就需要将每个客户限定计算一次,因此问题的分析就变成了预先计算之后的二次聚合。书中把这个问题进一步解释如下:

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 .

假定视图中使用“子类别”sub-category字段,问题如下:

各个子类别 的 平均客户消费年龄

这个问题中,包含了一个预先的问题,从而消除直接平均带来的多次计算:各个子类别、每个客户的消费年龄唯一值(去重)。这里的去重复可以创建一个临时表,也可以使用min或者avg聚合。

2、PBI的解答方法

在PBI中,先使用计算列(calculated column)计算“每个客户消费时的当下年龄”。它等于出生日期和订单日期的年数间隔。如果数据表中没有出生日期,可以使用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 
)

计算列中的字段,接下来可以等价于数据表字段,既可以作为维度,又可以作为度量。 不过,直接把上述字段计算平均是错误的,它会重复计算一个客户在同一时间的多次购买。即:

Avg Customer Age Wrong := AVERAGE (SALES[customer Age] )

官方还介绍了一种错误的做法:直接使用DINTINCT函数返回年龄的不重复值。

它会把相同年龄的多个客户列入一个值计算,相比上一种错误做法(客户被多次计算),这里的数据就会太少(相同年龄的客户会被忽视)。如下:

Avg Customer Age Wrong := 
AVERAGEX ( -- iterate on the distinct values of customer age
DISTINCT (Sales[customer Age]), 
SALES[customer Age] )

最后,作者使用了SUMMARIZE函数,指定聚合“每个客户、每个消费年龄”计算 年龄的平均值。如下所示:

Avg Customer Age correct:= 
AVERAGEX ( -- iterate on 
SUMMARIZE( --all the existing combinaiton
Sales, -- that exist in Sales
Sales[customer ID], -- of the customer id and 
Sales[customer Age], -- the customer age
)
SALES[customer Age] ) -- and average the customer's age
)

注意,这里SUMMARIZE的变量是数据表和维度字段(客户ID和客户年龄——来自calculated column)。 而当视图中行字段是Color时,行字段也成为了影响DAX计算的“筛选器上下文”(filter context)的一部分,因此聚合就从summarize指定的两个维度字段,变成了三个维度字段:

各个color, 的 客户平均年龄 (指定当前color、计算每个客户的一次消费年龄)。

可以看出,DAX中SUMMARIZE的filter context和可视化中color字段共同起作用。 添加图片注释,不超过 140 字(可选)

3、Tableau 的解答方法

如果是Tableau中,这个问题的过程也是一样的,首先行级别计算计算订单消费时的客户年龄;其次聚焦客户年龄,避免多次计算。

第一步,使用datediff计算客户消费时年龄 添加图片注释,不超过 140 字(可选)

第二步,创建视图(这里使用子类别代替上面的color)。

这里的难点也是计算。

年龄直接平均显然是不对的(一个客户同一年的多次购买会全部参与平均值聚合)(图中第一列)。

如果指定“每个子类别、每个客户”呢,假设我在20岁、21岁、22岁都买过打印机,此时的计算,会计算我三个值的平均,即(20+21+22)/3作为接下来进一步计算的前提。显然,我的平均值年龄再次平均,是没有业务意义的(图中第二列)。 添加图片注释,不超过 140 字(可选)

参考PBI上的SUMMARIZE,正确的逻辑是,先解决同一个客户同一年龄中的购买问题,即指定子类别、客户、年龄返回唯一值——这里使用AVG或者MIN都可以。假设我在20岁、21岁、22岁都买过打印机,并且在20岁还没过同一个类别下的打印机墨盒,那么就应该保留三个唯一值,即去重,又不能进一步简化到无意义。

所以正确的计算是:

AVG( {FIXED [Sub-Category],[Customer Name],[消费年龄]: avg([消费年龄])})

接下来,上述的返回值,在同一个子类别中,和其他所有客户的其他返回值计算平均值,就是该子类别的客户平均消费年龄。

基于这样的计算,一个客户、同一年龄的多次购买会被简化为一个值;而一个客户、多个年龄的购买会被保留。不同客户之间遵循相同的逻辑,最终获得聚合值。

在Tableau中,这里的子类别正是视图维度,因此可以用INCLUDE代替,如下:

AVG( {INCLUDE [Customer Name],[消费年龄]: avg([消费年龄])})

这样一看,这一简单的一行,就相当于PBI中的冗长的一大段,这里重复一下,强烈对比:

Avg Customer Age correct:= 
AVERAGEX (                           -- iterate on 
  SUMMARIZE(                           --all the existing combinaiton
    Sales, -- that exist in Sales
    Sales[customer ID],               -- of the customer id and 
    Sales[customer Age],                 -- the customer age
    )
  SALES[customer Age] )                   -- and average the customer's age
)

4、小结

基于这样的对比,读者应该能理解不同工具背后,对相同问题的处理方式其实如出一辙——这里都是引用预先聚合的二次聚合。只是,不同工具采用的技术路线,PBI采用了编程思维,Tableau使用了详细级别的层次逻辑。

简洁与否,一目了然。

谁更适合业务用户呢?


喜乐君 Nov 29, 2022 V1 关键案例

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

网站通过 WordPress.com 打造.

向上 ↑

%d 博主赞过: