从数据到信息
从信息到洞察

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

背景

在上文认识 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

YTDMTD 指标与去年同期对比是分析业绩的常用方法,如果用今天作为日期筛选的锚点,这个分析可以很容易的实现。但如果数据是每周刷新一次,应该如何处理呢? 假设您最近一次刷新是 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 报告


成品案例打包下载

付费或下载说明

此资源下载价格为120G 币,请先
VIP 地址 1:********
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入 VIP 会员可享受全站权益,性价比更高。

48
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
19 评论数
34 被回复的评论
18 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
游客
GJJ

高飞老师您好,想请教一下上面第13个–相对周期筛选,在计算2013YTD 2014YTD的代码中,要使用
ALL ( ‘Global Superstore2′[weeknum] ),我试了一下,不移除该筛选器也没问题啊?是不是因为您的模型没有使用日期表,我测试时手动创建了日期表。另外,我有测试了另一个模型,截图如下,感觉很奇怪,希望老师帮忙分析一下

游客
阿拉丁

请问第9个“计算期末值 Value on the last day of a period”使用的是powerbi自带的折线图么,我做的怎么放了两个度量值作为折线的取值后,ticket作为图例就拖不进去。请问您的6根线是如何生成的。

游客
智宁

在实际值对比目标值 Actual vs Target的案例PBI源文件中,没有PBI报告下方的各产品目标差距的图表?是后来新增了吗?

游客
智宁

总额的百分比的计算公式中,是不是这样写也可以:
Percent of total2 = DIVIDE([sales],CALCULATE([sales],ALL()))

成员
智宁

高飞老师,第15个案例成比例刷的tableau案例怎么不显示啊?

成员
152****5268

获客分析客户,tootip为什么显示的日期不正确呢

1657808981249.jpg
成员
138****3514

报告使用的数据集哪里可以下载到? 色

游客
lyliuyouyang

高飞老师你好 获客分析 New customer acquisition 我自己也反复理解了一下这个表达式,感觉是大致理解到了,但是表达式有点复杂 我改了一下,取消了calculatable,如下图所示,但是这样返回不了正确的结果。 如下图,度量值2 = COUNTROWS ( … 阅读更多 »

游客
HYR

高飞老师请问一下,您的可视化地图是用的哪个组件呀?还有哪些可视化怎么没有右上的焦点和筛选按钮呢

游客
GJ

高飞老师,你好, 请教一下: 1.动态区间对比是哪个可视化组件呢? 2 日期选择如何实践自动阴影的效果呢?同时如何实现文字中的值也跟着变化呢?

成员
lyliuyouyang

同期群分析 Cohort Analysis这一部分,高飞老师能不能重点讲一下这个表达式,感觉虽然不长,但是很难理解。就我个人的水平来说,暂时无法理解。其实想要做出这个图来,是比较困难的,比如应该是先去求得每个[Customer ID]的最早购买记录的年度,然后再通过年度对[Customer ID]进行一个分组,最后再去按照年份去生成图表,但是我自己还是没办法独立写出这个表达式。麻烦高飞老师再点拨一下

成员
138****3514

资源显示VIP8折,实际付款的时候价格未变,建议调整价格说明 微笑

成员
黄时

请问配色能否讲一下?微软原生配色辣眼睛

游客
天堂向左

回头客分群分析,pbi的0次复购数据好像做错了,跟tableau的对不上,我自己利用辅助表做的倒是跟tableau一样

游客
BC

刚刚把数据集弄下来。
我的爪子已经迫不及待地想跟着飞哥学学了。

游客
AD

不知道能否列出都是哪个组件,感觉真的不是一个POWER BI ,还能实现这么漂亮的图表。

游客
大M

飞哥用的Power BI跟我们的不是同一个Power BI……

如果能把每个图用到的视觉对象列出来就更好了……现在Power BI视觉变现很大程度决定于所用的组件,懂了DAX算法还要懂用组件才行。