2021-10-18 16:39 已编辑周口师范学院数据分析师

关注

销售需求丨表格配色

BOSS：那个谁，那个什么茶？！

白茶：…（一脸懵）啊？！

BOSS：给我做个图，要求能给图表的数据自动标记颜色！

白茶：热…热力图？！

BOSS：什么玩意热了冷了的，就是表格，数字，打开就能给我标注不同段位的颜色，你滴，明白？！

白茶：（#￥%&*@#）明白！

最近在和一位小伙伴沟通的时候，遇到一个特殊的需求：

BOSS年龄大了，看不懂可视化插件，就希望能有一个表格，简简单单就行，但是需要让表格自动标记颜色。

白茶当时一合计，这不就是热力图么？只不过要求体现在表格当中。

注：感谢各位大佬指点，受益无穷。

白茶的扣扣群号：772646751

模拟数据：

这是本期所使用的模拟数据，一份销售明细，一份产品表，一份分店表。

将其导入到PowerBI中，建立日期表：

日期表 =
GENERATE (
    CALENDARAUTO (),
    VAR DA = [Date]
    VAR YEAR =
        FORMAT ( DA, "YY" ) & "年"
    VAR QUARTER =
        "季度" & FORMAT ( DA, "Q" )
    VAR MONTE =
        FORMAT ( DA, "MM" ) & "月"
    VAR DAY =
        DAY ( DA )
    VAR WEEKID =
        WEEKDAY ( DA, 2 )
    VAR YOU =
        YEAR ( DA )
    VAR TOL =
        FORMAT ( DA, "MM" )
    VAR TPO =
        FORMAT ( DA, "DD" )
    VAR YEARR =
        FORMAT ( DA, "YYYYMM" )
    RETURN
        ROW (
            "年度", YEAR,
            "季度", QUARTER,
            "月份", MONTE,
            "日", DAY,
            "年度季度", YEAR & QUARTER,
            "年度月份", YEAR & MONTE,
            "星期", WEEKID,
            "索引", YOU & TOL,
            "年月", VALUE ( YEARR )
        )
)

模型关系如下：

编写基本的代码：

销售金额：

销售金额 =
SUMX ( '销售明细', '销售明细'[销售数量] * RELATED ( '产品表'[销售价] ) )

销售成本：

销售成本 =
SUMX ( '销售明细', '销售明细'[销售数量] * RELATED ( '产品表'[成本] ) )

利润：

利润 =
[销售金额] - [销售成本]

动态数据：

动态数据 =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( '财务维度'[财务维度] ) = "利润", [利润],
    SELECTEDVALUE ( '财务维度'[财务维度] ) = "销售金额", [销售金额],
    BLANK ()
)

动态数据传送门：销售需求丨动态数据（修订）

将这些放在可视化面板中，结果如下：

到这里，我们的准备工作完成。

BOSS的需求是什么？为表格进行配色！

那么配色肯定需要有个依据吧？按照国内BOSS的需求，我们将依据划定为三点：最小值、平均值、最大值。

那么如何求平均值呢？要考虑当前上下文。

当前上下文，白茶使用了两个表里面的维度，分别是分店表以及日期表。这种情况下需要根据动态数据，在当前条件下计算出平均值，需要考虑两个维度的交叉效果。

提到交叉，小伙伴们想起来什么没？笛卡尔积啊！

编写如下代码：

均值 =
VAR TLL =
    GENERATE ( ALL ( '分店表'[分店名] ), ALL ( '日期表'[月份] ) )
RETURN
    AVERAGEX ( TLL, [动态数据] )

将其放在卡片图中查看：

1023871/12/14=6094.47，这个结果很显然是没问题的，稳妥起见，将其放入到表中对比：

问题来了，仔细看每一行的均值数据，会发现都不一样。

可能有的小伙伴说了，这没啥啊，不是很正常么？

其实到这里已经出问题了，我们需要让这个均值在当前上下文中呈现一个值，只有这样才能进行比较判定。

优化代码：

动态均值 =
VAR TLL =
    CALCULATETABLE (
        ADDCOLUMNS ( SUMMARIZE ( '销售明细', '日期表'[月份], '分店表'[分店名] ), "动态数据", [动态数据] ),
        ALLSELECTED ()
    )
RETURN
    AVERAGEX ( TLL, [动态数据] )

结果：

可以看得出来，这次的结果符合预期。

解释一下代码含义：

首先是利用SUMMARIZE函数构建了一个虚拟表，只有分店和月份维度，这一步的目的是为了构建笛卡尔积，并且不受当前上下文的筛选影响；

利用ADDCOLUMNS函数，为每一个笛卡尔积的结果匹配相关的数据；

利用CALCULATETABLE函数保持虚拟表始终处于被筛选状态；

最后利用AVERAGEX函数进行求平均值。

注：最大值最小值方法是一致的，后面不赘述。

利用上面的代码，求出最大值，最小值，然后编写如下代码：

配色 =
SWITCH (
    TRUE (),
    [动态数据] = [动态最小值], "#003366",
    [动态数据] > [动态最小值]
        && [动态数据] <= [动态均值], "#336699",
    [动态数据] > [动态均值]
        && [动态数据] < [动态最大值], "#99CCFF",
    [动态数据] = [动态最大值], "#0099FF",
    BLANK ()
)

选择动态数据，点击条件格式，选择背景色；

选择字段值，选择刚才写好的度量值，单击确定。

这样就完成了表格的配色，自动标注最大值，最小值，对各阶段的值进行不同配色。

当然，也可以根据个人喜好，调整自己喜欢的颜色。

最后，对于界面啊，切片器什么的等一些细节进行一下优化：

这样，一份热力图表格就新鲜出炉了。

样例传送门丨http://t.cn/A62Cj4U3

彩蛋：

1、累计排名是哪个可视化插件？

2、阈限计数如何添加？

彩蛋解答：

1、Animated Bar Chart Race

2、首先点击输入数据，建立合适的字段；

编写如下代码：

计数项目 =
VAR TLL =
    SELECTEDVALUE ( '统计项'[项目] )
VAR TOL =
    CALCULATETABLE (
        ADDCOLUMNS ( SUMMARIZE ( '销售明细', '日期表'[月份], '分店表'[分店名] ), "动态数据", [动态数据] ),
        ALLSELECTED ()
    )
RETURN
    SWITCH (
        TRUE (),
        TLL = "最大值", [动态最大值] & "元",
        TLL = "上限区", CALCULATE ( COUNTROWS ( FILTER ( TOL, [动态数据] > [动态均值] && [动态数据] < [动态最大值] ) ) ) & "个",
        TLL = "均值", FORMAT ( [动态均值], "0" ) & "元",
        TLL = "下限区", CALCULATE ( COUNTROWS ( FILTER ( TOL, [动态数据] < [动态均值] && [动态数据] > [动态最小值] ) ) ) & "个",
        TLL = "最小值", [动态最小值] & "元"
    )

结果：