​再谈 Power BI 分组的真实案例

• 2019 年 10 月 6 日
• 筆記

文本为叶云老师提供，并附有示例文件。

前言

之前已经简单与大家聊过 Power BI 中的分组问题了，近日朋友又问了一个实际工作中的问题，恰巧也与分组有关，便整理之后，与众位朋友共享，再谈 Power BI 分组的博大精深。

问题

需求

现在有一张事实表，样例如下

朋友想对各个产品ID的平均销售额进行分组操作，分成以下8组

朋友的解决方案

不难看出，事实表的每一行均为一张订单，所以先要得到一张中间表每个产品ID平均销售额的中间表。

于是朋友使用 Power BI 中的 DAX 构建表的方式，构建了一张中间表。公式如下：

中间表 =  SUMMARIZE ( '订单', '订单'[产品ID], "平均销售额", AVERAGE ( '订单'[销售额] ) )  

结果样例如下：

然后使用 DAX 增加计算列，公式如下：

分组列 =  SWITCH (      TRUE (),      '中间表'[平均销售额] < 100, "小于等于100",      '中间表'[平均销售额] >= 100          && '中间表'[平均销售额] < 500, "100 ~ 500",      '中间表'[平均销售额] >= 500          && '中间表'[平均销售额] < 1000, "500 ~ 1000",      '中间表'[平均销售额] >= 1000          && '中间表'[平均销售额] < 2000, "1000 ~ 2000",      '中间表'[平均销售额] >= 2000          && '中间表'[平均销售额] < 3000, "2000 ~ 3000",      '中间表'[平均销售额] >= 3000          && '中间表'[平均销售额] < 4000, "3000 ~ 4000",      '中间表'[平均销售额] >= 4000          && '中间表'[平均销售额] < 5000, "4000 ~ 5000",      '中间表'[平均销售额] >= 5000, "大于等于5000"  )  

之后就完成了数据准备，得到了下表：

之后仅需将分组列和产品ID的计数拖入表格即可得到结果。

朋友的困扰

但是老板的需求要是这么简单就好了，老板希望可以根据订单日期进行筛选，但是现在的这个方法，订单日期的筛选完全无效。

解法

看过我之前博文的战友应该能有些思路，这类问题，就应该使用 DAX 来解决，我们不做计算列，不做中间表，全都应该依靠 DAX 的模型能力。应了佐罗老师的一句名言：DAX 驱动可视化，非侵入式的模型设计。现在且听我娓娓道来。

准备

为了分组，当然要准备一张分组表，可用的方式有很多，例如再导入一张分组表等等，此处使用 DAX 表达式，构建一张分组表，公式如下：

分组表 =  DATATABLE (      "组别", STRING,      "最小值", INTEGER,      "最大值", INTEGER,      {          { "< 100", -999999, 100 },          { "100 ~ 500", 100, 500 },          { "500 ~ 1000", 500, 1000 },          { "1000 ~ 2000", 1000, 2000 },          { "2000 ~ 3000", 2000, 3000 },          { "3000 ~ 4000", 3000, 4000 },          { "4000 ~ 5000", 4000, 5000 },          { "> 5000", 5000, 999999 }      }  )  

其中 DATATABLE 函数用于构建一张表，前半部分用于说明表结构，后半部分用于填入数据。之后就得到了下表：

表达式详解

准备已成，那我们就可以构建 DAX 表达式了，分组仅用一个度量值，表达式如下：

分组方式 =  VAR CurrentItem =      SELECTEDVALUE ( '分组表'[组别] )  // 确定所在的组别  VAR LeftValue =      CALCULATE ( MIN ( '分组表'[最小值] ), '分组表'[组别] = CurrentItem )  //将该组的最小值作为左值  VAR RightValue =      CALCULATE ( MIN ( '分组表'[最大值] ), '分组表'[组别] = CurrentItem )  //将该组的最大值作为右值  VAR mid_table =      ADDCOLUMNS (          VALUES ( '订单'[产品ID] ),          "平均销售额", CALCULATE ( AVERAGE ( '订单'[销售额] ) )      )  // 按照产品ID构造一张虚拟中间表  VAR ItemRange =      FILTER ( mid_table, [平均销售额] >= LeftValue && [平均销售额] < RightValue )  // 过滤出属于最初选定组别的虚拟表的子集  RETURN      COUNTROWS ( ItemRange )  

可能有些伙伴没有看明白，我分布说明下：

1. 由于度量值最终会和组别一起使用，那么首先要确定是针对那个组进行统计

VAR CurrentItem =      SELECTEDVALUE ( '分组表'[组别] )  

1. 然后确定了组别，就需要确定这一组的左值和右值，一边后面用于计算

VAR LeftValue =      CALCULATE ( MIN ( '分组表'[最小值] ), '分组表'[组别] = CurrentItem )  VAR RightValue =      CALCULATE ( MIN ( '分组表'[最大值] ), '分组表'[组别] = CurrentItem )  

1. 之后构造一张虚拟表，是按照产品ID求销售额的平均值，行为类似上文中使用 SUMMARIZE 构造的中间表

VAR mid_table =      ADDCOLUMNS (          VALUES ( '订单'[产品ID] ),          "平均销售额", CALCULATE ( AVERAGE ( '订单'[销售额] ) )      )  

1. 最后要统计出属于该分组的一个子集，统计其行数就是产品ID的计数啦。

VAR ItemRange =      FILTER ( mid_table, [平均销售额] >= LeftValue && [平均销售额] < RightValue )  RETURN      COUNTROWS ( ItemRange )  

最后让我们一起来看看效果。

总结

最近都在写分组，但是分组却是工作中最常用的场景。战友们如果有工作中遇到的有趣的分组需求，欢迎在留言区留言，我们再交流，寻找模型驱动可视化的边界。

BI佐罗备注：PowerBI用作分析的动态性，SQL或其他分析工具对于分组都很简单，但唯有商业智能工具是天生自带动态性的。

——

延伸阅读：

PowerBI DAX 区间分组通用模式及正态分布曲线 史上最强 PowerBI 全动态 RFM 模型 2.2 版 完美得无懈可击 PowerBI 全动态 RFM 模型 2.0 版 震撼发布