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

理解 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[Weight Unit Measure] ),
    "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 的空行之后。所以,基于以上原因,请不要假设列中的空值一定对应汇总行。

ROLLUP 使用多列时,其汇总效果效似于矩阵的多层行标签,第一参数位于行标签的最上层,第二参数位于第二层,以此类推。所以 ROLLUP 参数的排列顺序会影响最终展现效果。

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 一文中,你将找到解决此类问题的最佳方案。

19
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
8 评论数
18 被回复的评论
7 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
GOGOGO

SUMMARIZE 和 ISSUBTOTAL product没加引号

成员
xifeng

老师,我想计算销售金额大于50的销售记录中各个产品大类的销量,然后采用ADDCOLUMNS函数的话我可以完美掌握各个计值环境,但是使用SUMMARIZE函数就有点力不从心的感觉,特别是第四个查询,完全出乎了我的预料。

所以,我想问一下:
1、在SUMMARIZE函数中添加的计算列的计值环境有哪些?
2、上述的计值环境是怎样得到的?

模型与查询结果如下图,希望老师能帮我解惑,谢谢

模型.jpg
查询.jpg
游客
王志峰

高飞老师,我有一个数据源(成绩表),我想根据姓名字段筛选数据源,创建动态的summarize表,对聚合的平均分进行分组(不同分数段对应不同等级),然后用姓名和等级构成新的字段,但是我遇到了两个困难:
1.姓名字段无法筛选summarize表 除非将表函数定义为变量
2.将表函数定义为变量后,虚拟表没有字段的含义,无法进行操作。

成员
lyliuyouyang

高飞老师,我这两天在实际应用中发现summarize在和时间智能函数同时使用的过程中,出现了一个很奇怪的现象:我使用了summarize去生成查询表的时候累计值返回不了正确的结果,我定义了2个度量值:[总数]=sum(‘表1′[数量]) [累计总数]=var maxdate=max(‘日历'[Date]) return CALCULATE([总数],’日历'[Date]<=maxdate) ,然后通过和日期表建立关系。table1和table2其实是一个意思,都是累计总数返回了错误值,就是累计总数和总数居然完全相同,但是table3使用summarizecolumns,不把事实表作为筛选表就可以返回正确的累计值,这个地方很迷惑。其实之前完全没有注意这个问题,因为直接生成数据透视表的时候没有这个问题。但是用summarzie查询问题就暴露出来了,请问这种情况到底是什么原因呢?
我试了一下把累计值不用变量的写法也不行( [累计总数]CALCULATE([总数],FILTER(all('日历'[Date]),'日历'[Date]<=max('日历'[Date])))这个还是同样的效果)。

批注 2020-04-22 093334.jpg
批注 2020-04-22 093427.jpg
成员
lyliuyouyang

最近在利用表函数写dax查询语句的时候遇到以下问题:在excel中编辑查询语句不能使用var的变量,我先在dax studio中编辑好了语句,在dax studio中可以返回正确的结果,但是把语句粘贴到excel中,虽然可以返回想要的结果表,但是这个时候启用刷新,就会卡死excel,每次都必然卡死,但是改写查询语句,使语句里面不含var变量,查询和刷新都没问题。
我在excelhome和微软官方都提问了,但是暂时还没得到解答,麻烦看看我的疑惑,非常感谢!

https://answers.microsoft.com/zh-hans/msoffice/forum/all/excel%e7%9a%84dax%e6%9f%a5%e8%af%a2%e5%88%b7/de2d9535-c25a-4475-960f-f7e9fb18d424

http://club.excelhome.net/thread-1523121-1-1.html

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数