Tableau Top 15大详细级别表达式案例解读与复刻-PowerBI极客

背景

在上文认识 Tableau 详细级别中我们介绍过详细级别表达式赋予了 Tableau 极大的分析灵活性，为了加深用户的理解，官方博客提供了 15 个配套的实战案例，这些案例兼顾实用性和难度，是理论和实践的精彩结合。每个问题既有一定的业务价值，又需要你开动脑筋，绝非轻易可以回答。正因如此，使用 PowerBI 解决他们仍然有很大的价值。

本文介绍如何使用 Power BI 完成这 15 个案例，展示从计算公式到最终报告的完整过程。相同的命题，不同的解法带来不同思维方式之间的碰撞，这是一次对比两种工具的绝佳机会。

在计算逻辑的定义方面，Power BI 将其完整的抽象到度量值中，而 Tableau 则是借助详细级别表达式和 GUI（用户界面）共同完成；
在报告层，我将两种工具制作的案例报告同时展示在页面中，点击按钮切换。Tableau 的可视化以灵活性见长，但你会发现这次 Power BI 并没有落后太多，一些高难度的报告完全可以做出类似效果。

因为嵌入的报告数量较多，网页需要从微软和 tableau 服务器调用数据，首次打开请耐心等待报告加载，Tableau 报告顶部的故事线可能偶尔出现加载不完整的情况，点击箭头即可恢复正常。

报告使用的数据集

Coffee Chain 产品销售数据：记录每种产品在不同地区的库存、销售、利润等指标的实际完成和目标数据
Global Superstore、SuporeStore 订单明细数据：记录每笔订单的交易详情，主要字段有订单 ID、产品 ID、顾客 ID、下单时间、金额、数量等，主键为订单 ID 合并产品 ID
Sales by Rep 销售代表的成单明细数据：主要字段有国家、订单 ID、代表信息、销售额等，主键为订单 ID 合并国家
Ticker data：三只股票 2012-2014 年的每日收盘价

9 月 28 日更新，全部 15 个案例的成品文件在文末提供付费下载，本文已经讲解了公式和它背后的逻辑，如果你希望一步到位了解所有可视化的秘密，欢迎付费支付作者

客户购买频率 Customer order frequency

从订单明细表中统计每个客户的购买次数相对简单，但如果我们要按购买次数统计对应的客户数量，该如何操作呢？一个显而易见的困难是，购买次数这个维度并不在原始数据中，为了解决这个问题，需要导入一张表，表中预先包含所有购买次数，在 Power BI 中我们把这样的表称为参数表。接下来的案例中你将多次看到参数表的应用。

新建参数表的方式通常有两种，新建数值型参数使用建模选项卡的新建参数功能；新建文本型参数使用输入数据功能。

本例使用数值型参数表作为维度，将自动生成的度量值带入公式，逐个客户计算是否满足购买次数要求，最后计算满足条件的客户数量，即可得到结果。

purchase time = SELECTEDVALUE('1 purchase time'[purchase time])  //由参数表生成

Number of Customers =
COUNTROWS (
    FILTER (
        VALUES ( SuperStore[Customer ID] ),
        CALCULATE (
            DISTINCTCOUNT ( SuperStore[Order ID] )
        ) = [purchase time]
    )
)

Power BI 报告

Tableau 报告

同期群分析 Cohort Analysis

合作时间越长的客户对销售额的贡献越大吗？我们按照客户首次购买的年份将客户分组，以便对比各个群组的年度销售贡献额。每个客户第一笔订单的日期作为首次购买日期。

同期群分析：对具有相同行为特征的用户分群后所做的专题分析

与上一个案例类似的是，首次购买年份并没有出现在原始数据中，需要引入一张首次购买年份的参数表。

Annually sales by first orderdate =
CALCULATE (
    [Sales],
    FILTER (
        VALUES ( 'Global Superstore'[Customer ID] ),
        CALCULATE (
            MIN ( 'Global Superstore'[Year] ),
            ALL ( 'Global Superstore'[Year] )
        ) = [Cohort 值]
    )
)

第五行的 VALUES 在当前筛选上下文中计值，第八行的 ALL 函数忽略当前年份筛选器，始终得到顾客的首次购买年份。

Power BI 报告

Tableau 报告

每日利润 KPI Daily profit KPI

我们可以很容易的查看利润随时间的变化趋势，为了进一步观察季节对利润的影响，我们想按天计算利润，然后从月级别聚合利润达标的天数，这该如何操作？

本例中，日期按盈利情况被划分为三种类型：Highly Profitable Days、Profitable Days、Unprofitable Days. 每种类型单独计算符合条件的天数。

本例需要在参数表中设定三种利润类型，由于报告默认在月级别计算，只需要将公式的计值粒度指定到日级别。最终的报告在展示时使用了一定的技巧。

Highly Profitable Days =
IF (
    SELECTEDVALUE ( '3 Daily profit KPI'[Category] ) = "Highly Profitable Days",
    COUNTROWS (
        FILTER (
            VALUES ( SuperStore[Order Date] ),
            [Profit per day] > 2000
        )
    )
)

Profitable Days =
IF (
    SELECTEDVALUE ( '3 Daily profit KPI'[Category] ) = "Profitable Days",
    COUNTROWS (
        FILTER (
            VALUES ( SuperStore[Order Date] ),
            [Profit per day] < 2000
                && [Profit per day] >= 0
        )
    )
)

Unprofitable Days =
IF (
    SELECTEDVALUE ( '3 Daily profit KPI'[Category] ) = "unprofitable Days",
    COUNTROWS (
        FILTER (
            VALUES ( SuperStore[Order Date] ),
            [Profit per day] < 0
        )
    )
)

Power BI 报告

Tableau 报告

总额的百分比 Percent of total

各个国家的收入如何影响全球销售额？如果我们按照贡献的百分比着色，可以看出美国对全球销售收入的贡献最大。但是，我们仍然需要关注在欧盟这些绝对值贡献较小的市场上，各个国家的销售额占全球总销售额的比例。

使用 ALL 函数，我们可以很容易的从公式中忽略区域筛选器的影响，始终返回当前国家占全球总销售额的百分比

Percent of total =
DIVIDE (
    [sales],
    CALCULATE (
        [sales],
        ALL (
            'Global Superstore'[Country],
            'Global Superstore'[Market]
        )
    )
)

Power BI 报告

Tableau 报告

获客分析 New customer acquisition

各区域市场每日获取新客户的总量是如何变化的？了解这一数据趋势，可以帮助我们了解地区营销和销售组织在发展新业务方面的表现。线越陡，说明获取的趋势越好。如果线逐渐变平，则必须采取一些措施来增加潜在客户流。

本例中统计的是新客累计数，即分市场、按天统计新客的累计数量。编写度量值的时候需注意排除 X 轴日期上下文对计值流的影响，公式可以有多种写法

Cumulative number of  new customers =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'Global Superstore'[Customer ID] ),
            ALL ( 'Global Superstore'[Order Date] )
        ),
        CALCULATE (
            MIN ( 'Global Superstore'[Order Date] ),
            ALL ( 'Global Superstore'[Order Date] )
        )
            <= MAX ( 'Global Superstore'[Order Date] )
    )
)

Power BI 报告

Tableau 报告

销售额对比分析 Comparative sales analysis

将各品类的销售额与整体平均销售额进行对比并不困难，但如果我们对比的目标是某个选定的类别，该如何操作？我们需要隔离这个选定类别，以免此操作影响品类销售额的计算。

你需要新建一张供筛选使用的品类参数表，通过 Treatas 函数与品类销售额关联起来

Comparative sales =
SUM ( SuperStore[Sales] )
    - CALCULATE (
        SUM ( SuperStore[Sales] ),
        TREATAS (
            VALUES ( '6 Comparative sales analysis'[Product Sub-Category] ),
            SuperStore[Product Sub-Category]
        )
    )

Power BI 报告

Tableau 报告

最大交易额的平均值 Average of top deals by sales rep

每个销售代表达成的所有订单中最大订单的金额是多少？然后考虑这样一个问题，对于各个销售代表达成的最大交易额，按国家/地区计算的平均值是多少？

使用迭代函数 Averagex 从销售代表粒度计算最大交易额，可以很快得到答案。

Average top deals =
AVERAGEX (
    VALUES ( 'Sales by Rep'[Sales Rep] ),
    CALCULATE ( MAX ( 'Sales by Rep'[Sales] ) )
)

Power BI 报告

Tableau 报告

实际值对比目标值 Actual vs Target

我们统计了连锁咖啡厅在每个州的实际利润与目标利润之间的差异。从报告左上方的图表中可以清楚地看出哪些州超额完成目标（蓝色），哪些州未完成目标（红色）。但是采用这种聚合方式会漏掉重要的细微差别：超出目标的原因可能是因为在该州销售的大部分产品都超出目标，也可能是由于个别明星单品的突出表现，弥补了未完成目标的其他产品。

为了研究这个问题，我们需要统计每个州超出目标的产品占比，方法是使用 FILTER 函数将计算的级别指定到产品粒度，计算每个产品的利润是否达标，并过滤掉未达标的产品。最终结果展现为灰色条形图。

Qualified percent of total =
DIVIDE (
    COUNTROWS (
        FILTER (
            VALUES ( 'Coffee Chain'[Product] ),
            [Difference] > 0
        )
    ),
    DISTINCTCOUNT ( 'Coffee Chain'[Product] )
)

Power BI 报告

Tableau 报告

计算期末值 Value on the last day of a period

表示某天状态的数据（如库存数、员工实际人数或存货的日清算值）在汇总时不能直接聚合，我们把这类指标称为半累加度量值。它们需要与可以聚合的普通指标（例如销售额或利润）区别对待。处理半累加度量时，您可能希望总显示周期最后一天的值。

半累加度量通常不能从时间维度聚合，但是可以从其他维度聚合，比如库存数可以按产品累加但不能按天累加。

我们记录了三支股票的日收盘价，下面的报告对比日收盘价均值（实线）和周期最后一天的收盘价（虚线）。

报告以月为时间轴，使用 Lastdate 可以方便的计算出期末值。

Average daily close value = AVERAGE('ticker data'[Adj Close])

Closing balance = CALCULATE(SUM('ticker data'[Adj Close]),LASTDATE('ticker data'[Date]))

Power BI 报告

Tableau 报告

回头客分群 Return purchase by cohort

获取新客户的成本可能非常高，因此我们希望能够确保现有客户会重复购买。过了一个、两个、三个、N 个季度后重复购买的客户数是多少？从未重复购买的客户数目是多少？如果按季度分群划分重复购买行为，会怎么样？

Number of Return Customer =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            FILTER (
                ADDCOLUMNS (
                    VALUES ( 'Global Superstore'[Customer ID] ),
                    "date of firstbuy", CALCULATE (
                        MIN ( 'Global Superstore'[Order Date] ),
                        ALLEXCEPT (
                            'Global Superstore',
                            'Global Superstore'[Customer ID]
                        )
                    )
                ),
                CONTAINS (
                    VALUES ( 'Global Superstore'[Order Date] ),
                    'Global Superstore'[Order Date], [date of firstbuy]
                )
            ),
            "date of secondbuy",
            VAR x = [date of firstbuy]
            RETURN
                CALCULATE (
                    MIN ( 'Global Superstore'[Order Date] ),
                    'Global Superstore'[Order Date] > x,
                    ALLEXCEPT (
                        'Global Superstore',
                        'Global Superstore'[Customer ID]
                    )
                )
        ),
        DATEDIFF (
                [date of firstbuy],
                [date of secondbuy],
                QUARTER
        ) == [10 Return purchase by cohort Value]
    )
)

10 月 7 日更新公式，修复原公式逻辑判断中的漏洞，简化判断规则

Power BI 报告

Tableau 报告

动态区间对比 Percent difference from average across a range

示例 6 介绍了如何对比单个选定项，但是如果你想对比的是一组区间值，该如何实现？例如，在行业重大事件发生前，您可能想对比股票的日收盘价与一段选定日期内的平均收盘价。

可以自定义的区间来自一张日期参数表，不需要与源数据建立关系，使用 Treatas 将数据映射到源表。

average closevalue for givenselection =
CALCULATE (
    AVERAGE ( 'ticker data'[Adj Close] ),
    TREATAS (
        VALUES ( 'A2 Percent difference from average across range'[Date] ),
        'ticker data'[Date]
    )
)

Difference% =
DIVIDE (
    AVERAGE ( 'ticker data'[Adj Close] ) - [average closevalue for givenselection],
    [average closevalue for givenselection]
)

Power BI 报告

Tableau 报告

相对周期筛选 Relative period filtering

将 YTD 或 MTD 指标与去年同期对比是分析业绩的常用方法，如果用今天作为日期筛选的锚点，这个分析可以很容易的实现。但如果数据是每周刷新一次，应该如何处理呢？假设您最近一次刷新是 3 月 1 日，但当前日期是 3 月 7 日。计算 MTD 使用前一年 3 月 1 日到 3 月 7 日的数据与今年的 3 月 1 日的对比，这会导致结果失真。

报告以计算 YTD 为例，2014 年数据截止到 8 月 23 日，所以 2013 年也需要截止到相同日期。

2013YTD =
VAR maxdate =
    MAX ( 'Global Superstore2'[Order Date] )
RETURN
    CALCULATE (
        CALCULATE (
            SUM ( 'Global Superstore2'[Profit] ),
            DATESYTD ( 'Global Superstore2'[Order Date] ),
            ALL ( 'Global Superstore2'[weeknum] )
        ),
        //'Global Superstore2'[year] = 2013,
        'Global Superstore2'[Order Date] <= maxdate - 365
    )

2014YTD =
CALCULATE (
    SUM ( 'Global Superstore2'[Profit] ),
    DATESYTD ( 'Global Superstore2'[Order Date] ),
    ALL ( 'Global Superstore2'[weeknum] ),
    'Global Superstore2'[year] = 2014
)

Power BI 报告

Tableau 报告

用户登陆周期 User login frequency

每月一次、每两月一次、每三月一次（依次类推）登录网站或应用程序的用户数是多少？平均登录率是多少？如何分布？原始数据粒度为每个用户 ID 的登录日期。也就是说，用户每天的登陆都记录为一行。生成此视图需要按照两次登录的平均间隔时间划分客户数，即我们必须按照某个度量划分另一个度量。

本例也是参数表的典型应用，平均间隔时间的数值取自参数表，度量值计算每个用户的平均间隔时间并在每个间隔处返回符合要求的客户计数。

Count of customer =
COUNTROWS (
    FILTER (
        VALUES ( 'Global Superstore_logindata'[User ID] ),
        VAR mindate =
            CALCULATE ( MIN ( 'Global Superstore_logindata'[Log in Date] ) )
        VAR maxdate =
            CALCULATE ( MAX ( 'Global Superstore_logindata'[Log in Date] ) )
        VAR duration =
            DATEDIFF ( mindate, maxdate, MONTH )
        VAR times =
            CALCULATE ( COUNTROWS ( 'Global Superstore_logindata' ) )
        RETURN
            ROUND ( DIVIDE ( duration, times ), 0 ) = [A4 User login frequency 值]
    )
)

Count% = DIVIDE([Count of customer],DISTINCTCOUNT('Global Superstore_logindata'[User ID]))

Power BI 报告

Tableau 报告

成比例笔刷 Proportional brushing

任何分析中最基本的问题都是：“对比的对象是谁？” 在筛选时，我们有时会将所选内容始终和总量对比，而不是简单地向下筛选所选内容。这种技巧称为按比例笔刷。

本例中的分母始终固定为整个亚太地区的销售额，借助 ALL 函数，这很容易实现。

TOTAL SALES = CALCULATE(SUM('Global Superstore'[Sales]),ALL('Global Superstore'[Country]))

SALES% = DIVIDE(SUM('Global Superstore'[Sales]),[TOTAL SALES])

Power BI 报告

Tableau 报告

客户群组年度购买频率 Annual purchase frequency by customer cohort

如果合作时间按争取到客户的年份衡量，忠诚度按年度购买频率衡量，那么合作时间越长的客户越忠诚吗？从示例 1 中，我们可以精确了解购买过一次、两次（依次类推）的客户数目。然而，营销人员极少需要精确确定购买过五次产品的所有客户。相比之下，了解至少购买过五次产品的客户数目更有用。

另外，我们从示例 2 中了解到，2011 年争取到的客户最多，而 2014 年争取到的客户最少。查看客户的绝对数目只能重复理解同样的趋势。因此，我们应该将每个群组的客户总额百分比作为度量忠诚度的标准，这样会更意义。

Purchase number% =
DIVIDE (
    COUNTROWS (
        FILTER (
            FILTER (
                VALUES ( 'Global Superstore'[Customer ID] ),
                CALCULATE (
                    MIN ( 'Global Superstore'[Order Date (Years)] ),
                    ALL ( 'Global Superstore'[Order Date (Years)] )
                ) = [Year 值]
            ),
            CALCULATE ( DISTINCTCOUNT ( 'Global Superstore'[Order ID] ) ) >= [Purchase time 值]
        )
    ),
    COUNTROWS (
        FILTER (
            ALL ( 'Global Superstore'[Customer ID] ),
            CALCULATE (
                MIN ( 'Global Superstore'[Order Date (Years)] ),
                ALL ( 'Global Superstore'[Order Date (Years)] )
            ) = [Year 值]
        )
    )
)

Power BI 报告

Tableau 报告