【DAX】从SUM函数到聚合表达式：CALCULATE与SUMX-喜乐君-敏捷BI布道师

本文将重点解答如下几个问题：

能否在SUM之中嵌套if函数（SUM+IF)），实现SUMIF的效果？（可以）
如果只是单一字段，SUM和SUMX是否计算过程完全相同？（是的）
在包含筛选时，SUM+IF和SUMX的区别是什么？（性能）

本文参考DAX SUM and SUMX Function by PowerBIDocs （原文地址），部分内容和图片笔者补充。

https://powerbidocs.com/2020/10/11/dax-sum-and-sumx-functions/powerbidocs.com/2020/10/11/dax-sum-and-sumx-functions/

PBI中DAX函数SUM和SUMX都是聚合函数，它们属于数字和三角函数。

这里对比SUM和SUMX的差异，并说明为什么SUMX是一个更高级的、更关键的聚合函数——更准确地说，它是一种包含多个子函数、完成特殊分析需求的表达式（expression）。作为类编程语言，这也是PBI相比于Tableau等敏捷BI的关键所在。

一、 SUM DAX function聚合函数及表达式

SUM函数是一类聚合函数（aggregation function），用于计算一列中的所有数字之和（calculates the sum of all numbers in a column）。因此语法中引用字段列名称——这也是SUM函数的唯一变量（SUM support only single argument.）。

语法 : SUM(<Column>)

这里介绍一个案例。

1.1 使用SUM函数的度量值(a measure for SUM function)

这里SUM的变量只有一个，就是superstore数据的sales字段列。

TotalSales = SUM('Global-Superstore'[Sales])

拖拽上述的“TotalSales”度量列字段到PBI的视图（card visual）中。

随着视图中分类字段的变化，度量值的结果也有不同。如果视图中没有分类字段，那么就会显示全国数据的求和（如下图右侧所示）；如果视图中有分类字段，首先会显示每个分类的分组求和，作为辅助又会显示合计值（Total）。

【喜乐君解读】不管维度是什么，SUM作为聚合的本质不变，外部维度字段影响了SUM的分组和组内聚合值的大小。

我们常说的函数（function），就是指完成特定单一功能（function的另一种意思就是“功能”）的函数关键词，SUM就是SUMMARIZE的简称，完成跨行相加的聚合功能。与之类似的还有AVERAGE、MAX、MIN、COUNT等等，它们构成了最重要的聚合函数。

1.2 包含筛选的SUM函数（SUM function with Filter）

Step-1: 创建一个包含筛选的度量值（Create a measure to get the sales of “Furniture” category.）

【喜乐君】在聚合基础上增加筛选条件，在Excel有多种方法：在透视表中增加筛选、SUM+IF组合判断、SUMIF嵌套判断等多种方法，方法1 缺乏灵活性，方法2性能差，方法3兼具二者有点。DAX中的CALCULATE与之类似，它把筛选和聚合分开，提升了性能，又把二者组合在一起确保了灵活性。详见喜乐君文章： CALCULATE表达式（上）：从SUM、SUM+IF到SUMIF、CALCULATE演进史（2023-9）.

为了增加筛选范围，这里就把SUM函数嵌套到CALCULATE计算之中。CALCULATE的第一个参数是聚合，第二个就是聚合筛选条件（FILTER condition）。如下所示。

SUM with Filter =  
   CALCULATE( SUM('Global-Superstore'[Sales]),
               FILTER('Global-Superstore', 'Global-Superstore'[Category]="Furniture")
        )

如下图右侧所示，右侧虽然没有任何分类字段，但是由于包含了筛选，因此聚合结果的结果，就等于furniture的聚合值。

【喜乐君解】

「喜乐君」熟悉Excel的人更倾向于SUM+IF的方式（注意还不是SUMIF），如下所示。

SUM( IF 'Global-Superstore'[Category]="Furniture" 
     THEN 'Global-Superstore'[Sales]) 
     END)

这两种方式的本质差异是什么？为什么这里出现了Calculate，从聚合方式的角度看，它是函数还是服务聚合的表达式？Calculate和SUM+IF两种方式的差异是什么？沿着这几个问题，我们可以进一步理解筛选是如何深刻的影响聚合，并且理解筛选对于性能优化的重要性。

如下图所示，在SUM中嵌套IF的“筛选”是“真计算、伪筛选”，使用修改数据为0或者null，使其虽然参与了聚合计算，但是似乎没有参与计算一样，最终实现“间接筛选”；而FILTER方式则是“真筛选”，它减少了数据表的明细数量，所以在性能上表现更好。

CALCULATE表达式的方式，可以粗略地对应Excel中的SUMIF函数，通过把IF和SUM紧紧整合在一起，让不满足条件的数据无需参与计算，而不需要返回0或者null。只是SUMIF的逻辑常用于“固定条件”的聚合，而CALCULATE的逻辑具有了更高的灵活性，以至于FILTER可以来自于视图中的动态筛选、切片器，甚至其他。

当然，SQl中也有类似的逻辑，一种是SUM（if then end ），另一种是结合where子句。该主题的对比参考详见喜乐君文章： CALCULATE表达式（上）：从SUM、SUM+IF到SUMIF、CALCULATE演进史（2023-9）.

1.3 SUM DAX with AND function 多个筛选交集的聚合表达式

Step-1: 在furniture类别基础上，叠加“chairs”的子类别筛选。如下所示。

SUM with AND = CALCULATE(
   SUM('Global-Superstore'[Sales]), 
    FILTER ('Global-Superstore', 
            AND ('Global-Superstore'[Category] = "Furniture",
                 'Global-Superstore'[Sub-Category]="Chairs") 
            ) 
     )

Step-2: 把上述度量值拖入视图中，视图的分类字段是：类别、子类别。注意，在没有任何维度的视图中（右侧所示），聚合值的结果就是同时满足category= ‘furniture’ 和 sub-category =‘chairs’对应的销售额的总和。

【喜乐君】相比左侧的视图，右侧的聚合中，相当于包含了分类字段——维度作为筛选。两个相同位置的筛选，技术上一般是AND交集计算。

1.4 SUM DAX with OR function多个筛选条件并集的聚合表达式

这里把两个筛选器改为“或”的关系。逻辑如下：

SUM with OR = CALCULATE(
    SUM('Global-Superstore'[Sales]), 
    FILTER ('Global-Superstore', 
            OR ('Global-Superstore'[Category] = "Furniture", 
                'Global-Superstore'[Sub-Category]="Chairs")
           )
     )

由于chair子类别只是furniture类别的子类，这里的OR相当于忽略了小范围的筛选器，获得了“furniture的销售额总和”。

【喜乐君备注】多个条件，这里相当于集合运算OR，两个子集取并集。

二、SUMX DAX function迭代函数SUMX

在介绍了SUM之后，这里重点说明SUMX的不同。

SUMX是一个迭代函数（iterator function），返回数据表中每一行计算表达式的聚合值（the sum of an expression evaluated for each row in a table）。使用此类函数，可以一次性处理多个字段列（multiple columns）。比如数量和单价。

【喜乐君】相比SUM只能针对一个字段做聚合，SUMX实现了同时应用于多个字段。不过由于括号的存在，SUMX之中的计算一定是优先于外部的计算求和的。语法Syntax: SUMX(<table>, <expression>)

S no.	Parameter	Description
1	Table	The table containing the rows for which the expression will be evaluated.
2	expression	The expression to be evaluated for each row of the table.

这里开始一个案例：使用SUMX计算“合计销售额”。

-- Get Total sales to using SUMX function: 
Total Sale = SUMX(
      'Global-Superstore', 
      'Global-Superstore'[Sales]
   )

2.1 假设我们要计算sales和profit两个字段行级别的求和（row wise sum of “Sales” & “Profit” columns together），此时，迭代函数SUMX就可以在行级别完成相加计算。

2.2 获得指定年度的销售额 Get sales of particular year

2013 Sales =SUMX 
    (FILTER ( 'Global-Superstore',
        Year('Global-Superstore'[Order Date]) = 2013 ),
    'Global-Superstore'[Sales] 
)

【喜乐君】2013年的销售额，相当于在聚合之前，增加年度的筛选条件。聚合的对象依然是sales字段，但是聚合的范围相比之前有所减少——从默认的整个table到指定的明细行。我们把这个结构不同、只是数量级上的变化称之为FIlter筛选。

2.3 SUMX with AND function

【喜乐君】如果有多个筛选器，也只是在上述逻辑基础上，增加了筛选的复杂性。筛选的背后是集运算，典型的运算符是AND、OR。于是就有了下面的案例。使用AND计算多个范围的共同范围，然后再计算求和。

SUMX with AND = SUMX(
    FILTER ('Global-Superstore', 
         AND ('Global-Superstore'[Category] = "Furniture", 
             'Global-Superstore'[Sub-Category]="Chairs")
           ), 
     'Global-Superstore'[Sales]
)

2.4SUMX with OR function

同理，使用OR计算多个筛选条件的并集，然后再计算求和。

SUMX with OR = 
SUMX(
   FILTER ('Global-Superstore', 
       OR ('Global-Superstore'[Category] = "Furniture",
          'Global-Superstore'[Sub-Category]="Chairs")
        ),
  'Global-Superstore'[Sales] 
)

【喜乐君】如上图的结果所示，AND获得了“furniture类别且chairs子类别”的销售额总和，而OR获得了“furniture类别（或chairs子类别）”的销售额总和。

三、SUM和SUMX的计算逻辑值差异

为了方便理解，我们不妨把FILTER、AND/OR、SUM各种计算的过程以可视化的方式展现出来。

如下所示，SUM的对象是表中的指定列。这个表，既可以是之前指定的明细表，也可以是FITLER筛选后的临时表，甚至于基于AND/OR临时表的计算结果表——本质上，它们都是明细表。

为什么PBI没有采用之前Excel中的SUM+IF逻辑吗？

正如前面所讲，相比于SUM+IF，SUMX和CALCULATE都实现了筛选和聚合的分离，这个是大数据分析阶段的必然步骤。从这个角度看，SUM+IF和这里SUMX（Filter+SUM）的方便相比，弱在性能。对于大数据分析而言，性能是极其重要的。

我们可以把SUM+IF组合视为先行级别计算、再聚合的计算，这种行级别计算间接完成筛选，是“伪筛选”。SUM+IF的“真计算、伪筛选”的间接筛选，是以修改数据的方式实现的，不符合条件的也参与计算，只是临时表中的结果是空，所以性能上更差，因此没有作为标准用法。

而把SUMX的方式视为先筛选、再聚合的计算，这里的筛选是基于明细表的直接筛选，它真正地排除了不符合筛选条件的部分。

Jan, 2023 V 1 发布与知乎
Sep 6, 2023 修改V2

了解喜乐君的更多信息

订阅后即可通过电子邮件收到最新文章。

【DAX】从SUM函数到聚合表达式：CALCULATE与SUMX