再谈 Power BI 分组的真实案例

BI佐罗

发布于 2019-09-23 19:24:55

2.1K0

发布于 2019-09-23 19:24:55

文章被收录于专栏：PowerBI战友联盟PowerBI战友联盟

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

前言

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

问题

需求

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

订单ID	订单日期	邮寄方式	产品ID	销售额
US-2018-1357144	2019/4/27	二级	办公用-用品-10002717	129.696
CN-2018-1973789	2019/6/15	标准级	办公用-信封-10004832	125.44
CN-2018-1973789	2019/6/15	标准级	办公用-装订-10001505	31.92
US-2018-3017568	2019/12/9	标准级	办公用-用品-10003746	321.216
CN-2017-2975416	2018/5/31	二级	办公用-器具-10003452	1375.92
CN-2016-4497736	2017/10/27	标准级	技术-设备-10001640	11129.58
CN-2016-4497736	2017/10/27	标准级	办公用-装订-10001029	479.92
CN-2016-4497736	2017/10/27	标准级	家具-椅子-10000578	8659.84
CN-2016-4497736	2017/10/27	标准级	办公用-纸张-10001629	588
CN-2016-4497736	2017/10/27	标准级	办公用-系固-10004801	154.28
……	……	……	……	……

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

组别	最小值	最大值
小于等于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平均销售额的中间表。

于是朋友使用 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 )

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

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

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

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

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

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

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

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

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

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

总结

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

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

——