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 函数来指定要分组的列的列表 |
不推荐 |
添加的新列名称 | |
不推荐 |
同时在行上下文和筛选上下文中计值的新建列的表达式 |
示例用法
SUMMARIZE 对作为第一参数的表执行分组,方法是使用通过多对一或一对一关系可以访问的任何表的任何列。也就是说,任何可以通过 RELATED 函数访问的列都可以被使用,而你不必真的使用 RELATED。例如,以下查询返回每年销售的数量:
EVALUATE SUMMARIZE ( Sales, 'Date'[Calendar Year], "Quantity", SUM ( Sales[Quantity] ) )
结果只包含按日期表 Calendar Year 列统计的销售数量,其中每个 Calendar Year 都至少在销售表有一条对应记录。
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 和 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(ROLLUPGROUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName])), "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD]), "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount]) )
结果如下图所示:
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 一文中,你将找到解决此类问题的最佳方案。
高老师,请教下,我手里有张传统中国式报表,商品的销售明细报表,到SKU到天到店铺的,横跨几年的全部数据;
我现在的需求是:
–
需要把这些销售明细按照当前最近的财月汇总,即汇总后是 到SKU到财月到店铺,然后在计算每条数据的销售单价和销售折扣,再匹配价格带和折扣带,最后再分别按照 价格带/折扣带 汇总数据;另外财月最好可以允许筛选器自主筛选;
–
同时这些数据还要有去年同期的(财月同期),因为要进行同比,所有的价格带和折扣带要基于去年的数据来做划分,不能是今年数据对应的商品明细的去年数据;(两年的商品明细不一样)
–
这两天可头疼死我了,想用summarize,感觉也不行,特来请教搞老师
有个问题麻烦高老师帮忙看看,可能对您来说很简单,我折腾有段日子了,函数都换了个遍,网上也搜不到解说的。
先看下图:黑色背景的图是daxstudio,这里面我觉得没有问题,因为查询结果就是我想要的,其中对summarize我也替换过和addcolumns+summarize的组合试过,结果都一样。然后我把它写到powerbi中,用的是minx(tmp3,[@accamulate])返回值,就是第二张图了,但结果不对,注意上图的红色值是对的,是@accamulate的最小值,这是不意味着我的计算并没有错?
但注意列值,这一[test]列的值跟[@sumprofit]的值却是一样的,为什么返回的是[@sumprofit]的值 ,却又给出了@accamulate的最小值?
那么如何才能返回正确的[@accamulate]呢?
高老师,在讲rollupgroup的案例代码中是不是缺了该函数?SUMMARIZE(ResellerSales_USD,
ROLLUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName]),
“Sales Amount (USD)”, SUM(ResellerSales_USD[SalesAmount_USD]),
“Discount Amount (USD)”, SUM(ResellerSales_USD[DiscountAmount])
)
你好老师
EVALUATE
SUMMARIZE (
Sales,
‘Date'[Calendar Year Number],
“Short Year”, ‘Date'[Calendar Year Number] – 2000,
“Quantity”, SUM ( Sales[Quantity] )
)
summarize第二参数有行上下文,派生列表达式自动被calculate封装,所以这里sum在转换的筛选上下文中计值,但’Date'[Calendar Year Number] – 2000同样也被calculate封装吗?我觉得它应该是在行上下文中计值的,这应该怎么理解呢?在summarize中,对于派生列表达式,应用于行上下文还是筛选上下文,是否取决于表达式而不同呢?
SUMMARIZE 和 ISSUBTOTAL product没加引号
老师,我想计算销售金额大于50的销售记录中各个产品大类的销量,然后采用ADDCOLUMNS函数的话我可以完美掌握各个计值环境,但是使用SUMMARIZE函数就有点力不从心的感觉,特别是第四个查询,完全出乎了我的预料。
所以,我想问一下:
1、在SUMMARIZE函数中添加的计算列的计值环境有哪些?
2、上述的计值环境是怎样得到的?
模型与查询结果如下图,希望老师能帮我解惑,谢谢
高飞老师,我有一个数据源(成绩表),我想根据姓名字段筛选数据源,创建动态的summarize表,对聚合的平均分进行分组(不同分数段对应不同等级),然后用姓名和等级构成新的字段,但是我遇到了两个困难:
1.姓名字段无法筛选summarize表 除非将表函数定义为变量
2.将表函数定义为变量后,虚拟表没有字段的含义,无法进行操作。
高飞老师,我这两天在实际应用中发现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])))这个还是同样的效果)。
最近在利用表函数写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