前言

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

问题

需求

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

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

朋友的解决方案

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

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

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

结果样例如下：

P1_中间表.png

然后使用 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"
)

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

P2_计算列.png

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

P3_结果1.png

朋友的困扰

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

P4_朋友的困扰.gif

解法

看过我之前博文的战友应该能有些思路，这类问题，就应该使用 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 函数用于构建一张表，前半部分用于说明表结构，后半部分用于填入数据。之后就得到了下表：

P5_构建分组表.png

表达式详解

准备已成，那我们就可以构建 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 ( '分组表'[组别] ) 

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

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

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

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

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

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

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

P6_最终效果.gif

总结

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