到底有多少人在P站上学微积分？用Power BI矩阵计算重合率

发布于

数据分析的顶级思维

张旭本职工作是台湾高数课外补习班老师，因为把自己的教学视频传到了全球最大的成年人网站Pxxxhub，而意外收获了众多国内外高数学习者的关注。看似很荒唐的事情，背后却隐藏着这位数学老师强大的逻辑思维。刚开始录制网络视频是为了招生，但他把视频发在YouTube和Facebook上没有带来多少点击量，因为教培界内卷太严重，这类网站关于高数的教学视频太多。

上传到Pxxxhub不仅能博眼球，关键是Pxxxhub的核心用户就是年轻的大学生，这正是张旭的潜在客户，这两者高度重合。所以张旭的另类做法收到了出圈的效果，一年时间在P站上传了200多部作品，卖网课一年赚到750万新台币（约172万人民币），不光养活自己，还请了好几个员工运营频道。

引言

重合率其实就是另一个角度的相关性分析，最经典的案例是啤酒和尿布。真正的相关性分析其实也很简单，改天单独写一篇。今天只说一下重合率计算问题。

本文解决两个事情：

  1. 矩阵重合率计算
  2. 矩阵中的最大最小值突出显示

全文共2600字，预计阅读时间8分钟。

提出问题

用矩阵计算各个平台的会员重合率，比如平台A有10个值，平台B有5个值，双方重复2个，就是用重合数据2/（10+5-2）

数据表：

目标：

详细解答

第一步：

确定大方向：一定是用矩阵呈现，且需要两个会员平台的维度表。

这一点，我在上一篇文章（这里）中详细说过原因，用的是两章时间表。

因此先来生成两个维度表：

代码语言：javascript
复制
平台1 = VALUES(userdata[会员平台])
平台2 = '平台1'

并建立一对多关系：

拖到报表中的行与列：

第二步：

计算任意两个平台之间的交叉会员的个数：

代码语言：javascript
复制
SHARE = 
var p1=CALCULATETABLE(VALUES(userdata[会员用户名]),ALL('平台2'))
var p2=CALCULATETABLE(VALUES(userdata[会员用户名]),ALL('平台1'))
var share= INTERSECT(p1,p2)
return COUNTROWS(share)

思路与复购是完全一致的，使用的都是INTERSECT函数，同时注意ALL的使用：

当然，要注意的一点是，其实从左上到右下的这条线上的数据无意义，因为它本身就表示同一个平台的会员数量。

第三步：

计算交叉会员个数占两个平台会员总和的百分比。

这个度量值基本上就是由上面第二步接着往下写了：

代码语言：javascript
复制
SHARE% = 
var p1=CALCULATETABLE(VALUES(userdata[会员用户名]),ALL('平台2'))
var p2=CALCULATETABLE(VALUES(userdata[会员用户名]),ALL('平台1'))
var share= INTERSECT(p1,p2)
var share_percent=DIVIDE(COUNTROWS(share),COUNTROWS(p1)+COUNTROWS(p2)-COUNTROWS(share))
return if(share_percent<1,share_percent,BLANK())

两个平台的会员总和用的是SHARE/(P1+P2-SHARE)，这是最简单的写法。

其实对于DIVIDE的分母我们还可以这样写：

COUNTROWS(DISTINCT(UNION(p1,p2)))

甚至通过复杂度量值写出or的条件，我们就不赘述了。

另外，从左上到右下的这条线上的这些数必定是100%，我们就直接用if变空白了。 结果如下：

这样基本的结果就出来了。

第四步：

空白的位置填充为灰色。

我们可以用度量值的条件格式来实现，设置如果值为空，则背景色为灰色：

显示效果：

到这里基本上就结束了。

此时，用户提出了更高的要求：

重合度最高的标红色

安排！

第五步：

矩阵中的数据值最大的标红色，这个需求非常落地，在错综复杂的表格中，我们往往需要用户第一眼就能看到最大值和最小值，尤其是这种矩阵中每一列的数据是同一层级的情况。

思考：矩阵是二维的展示，不可能直接获取最大、最小值。因此一定要将二维矩阵变为一维的表格去计算，因为表格和矩阵只不过是透视和逆透视的关系。

我们将矩阵变为表，再按照从大到小的顺序排列：

（这个表同样也是虚拟表中要构建的表）

这样就能求出重合度最高的值。

但是度量值在每一个格子里只能取一个值，而不知道其他筛选器下的值，也就是它根本不知道自己是不是最大值或最小值。

那么这个问题必然是通过创建一个虚拟表的方式来实现：

思路：

在虚拟表中建立如上图所示的表，然后获取share%的最大值value_max，这个value_max是不随着筛选器改变而变化的，再用这个value_max和真实的表中的被两个筛选器筛选的share%值对比，两者相等的话，那么这个share%就是最大值，也就是要标红的值。

步骤：

  1. 使用CROSSJOIN获取两组平台的笛卡尔积，建立虚拟表；
  2. 在虚拟表上逐行扫描计算每一行的share%；
  3. 求出这张虚拟表上的share%最大值value_max；
  4. 逐行扫描这张虚拟表，如果share%=value_max，返回1，否则为空。
代码语言：javascript
复制
value.MAX = 
var share_table=
CALCULATETABLE(
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES('平台1'[会员平台]),
            VALUES('平台2'[会员平台])
        ),
        "share%",
        [SHARE%]
    ),
    ALLSELECTED()
)
var value_max=MAXX(share_table,[share%])
VAR is_value_max=IF([SHARE%]=value_max,1)
RETURN is_value_max

我们再用条件格式来设定一下：

最终结果：

结论

本文通过一个计算多个平台会员重合率的问题，讲解了遇到此类问题时的建模方法、INTERSECT函数的使用、条件格式的设置，同时本文也对比区分了表和矩阵的区别，以及最重要的：如何在矩阵中将最大值和最小值进行醒目的标注。

就像最后这张图一样，简单的一个度量值加上条件格式标注，可以显著提高报告的可用性。因此，任何时候，我们都需要站在报告使用者的角度，深入地思考问题，为他们提供最大的便利，为业务提供最优的支持。

就像文章开头所引用的新闻，在P站放微积分课程是因为这两者的受众高度重合，啤酒+尿布的各种组合也成为了市场营销中强有力的手段，而这些都是为了获取更清晰的用户画像，方便给客户推荐更多的产品。

这就是数据分析。

#max#sh#share#union