从数据到信息
从信息到洞察

SUMMARIZE

SUMMARIZE 用于生成数据汇总表,按一列或多列对数据进行分组,并且可以使用指定的表达式为汇总后的表添加新列。SUMMARIZE 是最常用的函数之一,但随着新函数的出现,它的适用范围相比最初已经大大缩小,出于性能考虑,建议你阅读本文后谨慎的使用 SUMMARIZE。

SUMMARIZE

SUMMARIZE ( <Table>, <GroupBy_ColumnName1>, [<GroupBy_ColumnName2>...], [<Name1>, <Expression1>], [...])
SUMMARIZE (
    <table>,
    <Group_by_Column1>, [<Group_by_Column2>], [...]
    [ROLLUP( <group_by_columnX>, [<group_by_columnY>], [...] )]
    [<column1_name>, { <column1_expression> | ISSUBTOTAL(<group_by_column> ) } ]
    [<column2_name>, { <column2_expression> | ISSUBTOTAL(<group_by_column> ) } ]
    [...]
)
参数 属性 描述
Table 输入表
GroupBy_ColumnName 可重复 要分组的列或调用 ROLLUP 函数来指定要分组的列的列表
Name 不推荐 添加的新列名称
Expression 不推荐 同时在行上下文和筛选上下文中计值的新建列的表达式

示例用法

SUMMARIZE 对作为第一参数的表执行分组,方法是使用通过多对一一对一关系可以访问的任何表的任何列。也就是说,任何可以通过 RELATED 函数访问的列都可以被使用,而你不必真的使用 RELATED。例如,以下查询返回每年销售的数量:

EVALUATE
SUMMARIZE (
    Sales,
    'Date'[Calendar Year],
    "Quantity", SUM ( Sales[Quantity] )
)

结果只包含按日期表 Calendar Year 列统计的销售数量,其中每个 Calendar Year 都至少在销售表有一条对应记录。

SUMMARIZE 对第一参数的扩展表执行分组,你可以使用扩展表包含的任何列作为分组参数

SUMMARIZE 定义派生列的表达式具有筛选上下文,因此不需要使 CALCULATE,而且,SUMMARIZE 还有一个行上下文可以访问用于分组的列值,如下面的示例所示:

EVALUATE
SUMMARIZE (
    Sales,
    'Date'[Calendar Year Number],
    "Short Year", 'Date'[Calendar Year Number] - 2000,
    "Quantity", SUM ( Sales[Quantity] )
)

最佳实践

出于性能考虑,不推荐使用 SUMMARIZE 直接对表达式求值,当需要新建列的时候,我们有两种更好选择,一是使用 ADDCOLUMNS,这种改写很简单,你只需为包含聚合运算的列添加 CALCULATE,以便将行上下文转换为筛选上下文。例如,你可以使用以下更高效的方式编写之前的查询:

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Date'[Calendar Year Number]
    ),
    "Short Year", 'Date'[Calendar Year Number] - 2000,
    "Quantity", CALCULATE ( SUM ( Sales[Quantity] )
    )
)

需要在度量值中使用 SUMMARIZE 的时候,通过 ADDCOLUMNS 新建列是最佳实践。另一种情况是在编写查询的时候,可以使用 SUMMARIZECOLUMNS 替代 SUMMARIZE。比如示例用法中的第一个查询可以改写为:

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Quantity", SUM ( Sales[Quantity] )
)

值得注意的是,SUMMARIZE+ADDCOLUMNS 的组合也可以用与编写查询,而 SUMMARIZECLOUMNS 却不能完全用于度量值,其中原因将在下篇文章中介绍。

所以,根据情境的不同,你有两种方式代替 SUMMARIZE。但无论何种情况,只将 SUMMARIZE 用于生成分组是最好的选择。

SUMMARIZE 和 ROLLUP

你可以指定一个或多个分组在 SUMMARIZE 函数的结果中包括汇总行(ROLL-UP),在这种情况下,你需要在 ROLLUP 函数中加入列表达式。对那些用于分组的列,汇总行的值为空值。例如,以下查询将添加一行,包含所有年份的总计,总计行的 Calendar Year 显示为空值:

EVALUATE
SUMMARIZE (
    Sales,
    ROLLUP ( 'Date'[Calendar Year] ),
    "Quantity", SUM ( Sales[Quantity] )
)
ORDER BY 'Date'[Calendar Year]

由于汇总行在分组列为空值,因此,如果对结果排序,汇总行可以在明细行之前显示。如果要汇总更多列,则必须将所有这些列指定为单个 ROLLUP 调用的参数,其他不需要汇总的分组列可以在 ROLLUP 调用之前或之后指定。换句话说,一个 SUMMARIZE 语句中只能使用一个 ROLLUP。例如,下面的查询计算 Category 和 Weight Unit Measure 的汇总,而不计算所有年份的总计(Calendar Year 在 ROLLUP 函数之外调用)。

EVALUATE
SUMMARIZE (
    Sales,
    'Date'[Calendar Year],
    ROLLUP ( 'Product Category'[Category], Product[Class] ),
    "Quantity", SUM ( Sales[Quantity] )
)
ORDER BY
    'Date'[Calendar Year],
    'Product Category'[Category],
     Product[Class] 

从前面的结果可以看出,仅仅检查列中的空值并不是标识汇总行的可靠方法。事实上,2009 年的 Computers 类别中有两行在 Weight Unit Measure 列是空白值。这两行中有一行对应于没有在产品表中指定 Weight Unit Measure 的 Computers 总数,另一行是 2009 年 Weight Unit Measure 列所有 Computers 数量的汇总。此外,排序子句并不保证这两行的顺序,在这种情况下,汇总行位于未指定 Weight Unit Measure 的空行之后。所以,基于以上原因,请不要假设列中的空值一定对应汇总行。

ROLLUPGROUP

ROLLUPGROUP 也可用于计算分组的小计。如果替代 ROLLUP 使用,ROLLUPGROUP 和 ROLLUP 效果相同,向 groupBy_columnName 列上的结果添加汇总行。但是,你可以在 ROLLUP 内部添加 ROLLUPGROUP()来避免计算指定分组的小计。

下面的例子只显示了所有年份和类别的总计,没有包含所有类别的每个年份的小计:

SUMMARIZE(ResellerSales_USD, 
        ROLLUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName]),  
        "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD]),  
        "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])  
)
对于表格模型, 使外键只包含查找表中的现有值是一种最佳做法。你可以应用星型架构中使用的经典转换, 在传统 BI 系统中,使用代理键代替自然键建立表间关系,事实表中 “未知” 成员的自然键映射为统一的代理键(已在维表中预设好)与维表建立关系。而依赖于内建表格机制在空行中显示数据是第二选择。

SUMMARIZE 和 ISSUBTOTAL

若要了解行是否是分组列的汇总结果,可以使用 ISSUBTOTAL 函数将列添加到结果中,该函数接受分组列作为参数,如果行包含小计值,则返回 TRUE;否则,返回 FALSE。例如,以下查询将添加两列,用于标识该行是否为分组列的汇总行:

EVALUATE
SUMMARIZE (
    Sales,
    'Date'[Calendar Year],
    ROLLUP (
        'Product Category'[Category],
        Product[Weight Unit Measure]
    ),
    "Quantity", SUM ( Sales[Quantity] ),
    "Category Subtotal", ISSUBTOTAL ( 'Product Category'[Category] ),
    "W.U.M. Subtotal", ISSUBTOTAL ( Product[Weight Unit Measure] )
)
ORDER BY
    'Date'[Calendar Year],
    'Product Category'[Category],
    Product[Weight Unit Measure]

正如你在结果中看到的,W.U.M. Subtotal 列对包含 2009 年 Computers 类别的所有 Weight Unit Measure 的总和所在的行返回 True,而对 2009 年 Computer 类别下 Weight Unit Measure 为空的行返回 False。ISSUBTOTAL 函数可以用于获取有关报表中结果条件格式的信息。

SUMMARIZE 的局限

除了性能方面的限制,SUMMARIZE 的另一个限制是不能聚合在其内部创建的派生列,例如,观察下面的查询,它应该为每个产品类别返回相关子类别平均价格的最大值。

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        ADDCOLUMNS (
            SUMMARIZE (
                Product, 
                'Product Category'[Category], 
                'Product Subcategory'[Subcategory] 
            ),
            "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
        ),
        'Product Category'[Category]
    ),
    "Max SubCat Avg Price", CALCULATE ( MAX ( [Average Price] ) )
)

查询会得到一个错误的 MAX ( [Average Price] )。因为在 DAX 只允许你聚合物理列,在聚合函数一文中,我们介绍过类似的等价表达式:

MAX ( table[expression] ) = MAXX ( table, table[expression] )

如果你尝试聚合在查询中新建的列(例如本例中[Average Price]),引擎将无法找到相应的 MAXX 函数。为了获得正确的结果,你需要避免调用嵌套的 SUMMARIZE

EVALUATE
ADDCOLUMNS (
    VALUES ( 'Product Category'[Category] ),
    "Max SubCat Avg Price", CALCULATE (
        MAXX (
            ADDCOLUMNS (
                SUMMARIZE (
                    Product,
                    'Product Category'[Category],
                    'Product Subcategory'[Subcategory]
                ),
                "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
            ),
            [Average Price]
        )
    )
)

改写后的查询得到了正确结果

但是这种写法的问题也很明显,代码冗长且计算效率不高。如果希望同时计算最低和最高平均价格,你需要复制两份 SUMMARIZE,这会导致更长的执行时间。在 GROUPBY 一文中,你将找到解决此类问题的最佳方案。

说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
  订阅本文评论  
提醒
DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数