|
Power BI财销一体分析——关联分析实践
欢迎来到Powerbi小课堂，前几期课程我们深入学习了各种财务、销售实用案例，本期我将带领大家整合分散知识点，逐步构建一个全面的分析模型，涵盖封面、登录页、导航页、CEO驾驶舱等模块，以及趋势、产品、畅销品等多维度分析，同时融入度量值嵌套、计算组、动态指标、TOPN分析等高阶技巧。今天我将为大家带来最全面实用的关联分析应用实践，通过挖掘消费者购买数据中商品关联规律的数据挖掘技术，主要用于零售和电商领域。其核心目标是发现顾客经常一起购买的商品组合，从而优化营销策略，接下来，让我们直接进入今天的实用技巧分享环节。如果您在实践过程中遇到任何问题，欢迎留言交流。请关注我们的公众号《BI研究所》，以获取更多案例更新和学习资源。
想要一份这样的可视化看板吗？想学吗？我教你呀！
案例背景：
我司作为专业运动自行车跨国销售企业，业务范围涵盖自行车、组件、配件及运动服装。当前，我们正致力于开发财销一体化分析系统，旨在通过技术手段实现财务与销售的紧密结合，促进数据共享与流程优化，进而提升决策效率与运营效率，优化资源配置，强化风险控制，为企业的可持续发展注入动力。
设计思路：
通过成本、销量、利润及销售维度精准掌控财务状况，从产品、客户及地域三个层面深入分析企业运营趋势，
- 准备基础数据
- 数据清洗
- 建立关系视图
- 个性美化设计
- 数据建模（度量值）
- 制作可视化报告
首先我们要理解关联分析是什么，关联分析是通过分析交易数据，找出形如“如果买了A，很可能也会买B”的规律，通常用于快速定位产品不同组合的市场接受程度。以下为关键参数的简要介绍：
常用指标：
- 支持度（Support）：表示商品组合在总销售记录中出现的频率。支持度越高，说明该商品组合被顾客同时购买的可能性越大，
置信度（Confidence）则反映了在购买商品A的条件下，顾客购买商品B的概率。置信度越高，意味着购买商品A的顾客同时购买商品B的可能性越大。
- 提升度（Lift）衡量的是商品A和商品B同时被购买的频率与它们各自独立被购买频率乘积的比值。当提升度大于1时，表明商品A和商品B之间存在正向关联；小于1时，则表明它们之间可能存在负向关联或相互独立。
典型应用场景
商品陈列优化：将关联性强的商品摆放在相邻位置（如啤酒和尿布经典案例）。
促销组合：设计捆绑销售或交叉推荐（如买手机推荐配件）。
库存管理：根据关联商品需求预测补货。
个性化推荐：电商平台的“买了还买”或“搭配购”功能。
常用算法
Apriori算法：通过逐层搜索频繁项集生成关联规则（需设定最小支持度/置信度）。
FP-Growth算法：利用频繁模式树（FP-Tree）提高效率，适合大数据集。
在建模之前，我们要先了解下此分析方法的难点，首先是关系搭建，我们需要建立两个关于产品的维度表，再确定一条关系可用的前提下，使另一条关系存在，但不可用，以及购买A产品的同时，也购买了B产品的运算怎样实现，当A产品与B产品相同时，怎样将其排除在运算之外。
★建立关系视图（此步骤很关键，关系到后续运算是否可以正常呈现）
公式：
购物篮辅助表 = SUMMARIZECOLUMNS('产品表'[产品],'产品表'[子类别],'产品表'[分类])
购物篮切片表 = '购物篮辅助表'
公式逻辑解析：
SUMMARIZECOLUMNS 是 DAX 中用于 按指定列分组并生成汇总表 的函数。它会根据你提供的列进行分组，并返回一个不重复的组合列表，在建立一个表函数，直接引用购物篮辅助表，那么从公式名称也不难看出，这两张表，一个是用于切片器，一个用于数据呈现。
如下图所示搭建关系，注意关系的虚实关联：
★数据建模：关联分析（又称购物篮分析）
公式：
重复的产品排除 = IF( SELECTEDVALUE( '购物篮切片表'[子类别]) = SELECTEDVALUE( '购物篮辅助表'[子类别]) , TRUE() , FALSE() )
公式逻辑解析：
这个公式用于 判断两个表中的子类别是否相同，并返回 TRUE（相同）或 FALSE（不同）。
通常用于 筛选或标记数据，例如：
在购物篮分析中，避免重复计算相同子类别的商品。
在可视化或计算中，仅保留符合条件的记录。
公式：
bayA类产品的客户数 =
VAR BAYA = CALCULATETABLE(
VALUES( '销售'[客户关键字] ) ,
USERELATIONSHIP('购物篮切片表'[产品] , '销售'[产品]) ,
ALL( '购物篮辅助表' ) // 排除另一个产品切片器的作用
)
RETURN IF( [重复的产品排除], BLANK() , COUNTROWS(BAYA ) )
bayA类产品的销售额 =
VAR BAYA = CALCULATE(
[总销售额],
USERELATIONSHIP( '购物篮切片表'[产品], '销售'[产品]) ,
ALL( '购物篮辅助表' ) // 排除另一个产品切片器的作用
)
RETURN IF( [重复的产品排除] , BLANK() , BAYA)
公式逻辑解析：以bayA类产品的客户数为例
该公式用于计算 购买“bayA类产品”的唯一客户数，并满足以下条件：
如果 [重复的产品排除] 为 TRUE()（即当前产品子类别重复），则返回 BLANK()（不显示数值）。
否则，返回购买该产品的客户数量。
变量1：CALCULATETABLE：创建一个临时表，应用指定的筛选条件。
VALUES('销售'[客户关键字])：返回“销售”表中 不重复的客户列表（即购买过产品的客户）。
USERELATIONSHIP('购物篮切片表'[产品], '销售'[产品])：
临时激活 购物篮切片表[产品] 和 销售[产品] 之间的 非活动关系（需在数据模型中预先定义但未启用），用于筛选当前产品对应的销售记录。
ALL('购物篮辅助表')：
忽略 购物篮辅助表 上的任何筛选器（例如，避免其他切片器影响计算结果）
返回结果：
[重复的产品排除]：
如果为 TRUE()（表示当前产品子类别重复），则返回 BLANK()（不显示数值）。
COUNTROWS(BAYA)：
否则，计算 BAYA 临时表中的行数（即购买该产品的唯一客户数）。
重要知识点
通过 USERELATIONSHIP 临时使用 购物篮切片表[产品] → 销售[产品] 的关系（而非默认关系），确保筛选基于用户选择的“产品”。
忽略无关筛选器
ALL('购物篮辅助表') 用于排除其他切片器（如基于 购物篮辅助表 的筛选）对计算的干扰。
重复产品排除逻辑
如果 [重复的产品排除] 为真（例如，同一子类别的产品被重复计算），则隐藏结果，避免数据冗余。
公式：
bayB类产品的客户数 =
IF(
[重复的产品排除],
BLANK(),
CALCULATE(
DISTINCTCOUNT('销售'[客户关键字]),
KEEPFILTERS('产品表') // 关键修正：保持产品表的筛选上下文
)
)
bayB类产品的销售额 = IF(
[重复的产品排除],
BLANK(),
CALCULATE(
[总销售额],
KEEPFILTERS('产品表') // 关键修正：保持产品表的筛选上下文
)
)
公式逻辑解析：以bayB类产品的客户数为例
IF([重复的产品排除], BLANK(), ...)
这是一个条件判断，如果[重复的产品排除]为真，则返回BLANK()（空值）
否则执行CALCULATE部分的计算
CALCULATE(DISTINCTCOUNT('销售'[客户关键字]), KEEPFILTERS('产品表'))
CALCULATE：修改筛选上下文的核心函数
DISTINCTCOUNT('销售'[客户关键字])：计算"销售"表中"客户关键字"列的唯一值数量（即不重复的客户数）
KEEPFILTERS('产品表')：这是一个关键修正，它确保在计算时保持对"产品表"的现有筛选上下文
公式：
关联销售额 =
VAR SelectedProduct = SELECTEDVALUE('购物篮切片表'[产品])
VAR OrdersWithA =
CALCULATETABLE(
VALUES('销售'[客户关键字]),
USERELATIONSHIP('购物篮切片表'[产品], '销售'[产品]),
ALL('购物篮辅助表')
)
RETURN
IF(
[重复的产品排除],
BLANK(),
CALCULATE(
SUM('销售'[销售额]),
TREATAS(VALUES('购物篮辅助表'[产品]), '销售'[产品]),
OrdersWithA
)
)
公式逻辑解析：
变量1：VAR SelectedProduct = SELECTEDVALUE('购物篮切片表'[产品])
获取当前筛选上下文中'购物篮切片表'[产品]的选定值
如果有多值则返回空
变量2：VAR OrdersWithA = CALCULATETABLE(...)
定义一个包含特定客户关键字的表
CALCULATETABLE(VALUES('销售'[客户关键字]),...)：获取'销售'表中不重复的客户关键字
USERELATIONSHIP('购物篮切片表'[产品], '销售'[产品])：临时激活这两个表之间的产品关系
ALL('购物篮辅助表')：移除对'购物篮辅助表'的所有筛选
返回结果：
IF([重复的产品排除], BLANK(), CALCULATE(...))
如果[重复的产品排除]为真，返回空值
否则执行CALCULATE计算
CALCULATE(SUM('销售'[销售额]), TREATAS(...), OrdersWithA)
SUM('销售'[销售额])：计算销售额的总和
TREATAS(VALUES('购物篮辅助表'[产品]), '销售'[产品])：
将'购物篮辅助表'[产品]的值视为'销售'[产品]的筛选条件
动态创建两个列之间的关系
OrdersWithA：应用之前定义的客户筛选条件
重点高阶公式：
USERELATIONSHIP：临时激活非活动关系，这里建立了'购物篮切片表'和'销售'表之间的产品关系
TREATAS：动态创建筛选条件，将一列的值视为另一列的筛选器
CALCULATETABLE：在修改的筛选上下文中生成表
公式：
关联客户数 =
VAR CustomersA =
CALCULATETABLE(
SUMMARIZE('销售', '销售'[客户关键字]),
USERELATIONSHIP('购物篮切片表'[产品], '销售'[产品]),
ALL('购物篮辅助表')
)
VAR CustomersB =
CALCULATETABLE(
SUMMARIZE('销售', '销售'[客户关键字]),
KEEPFILTERS('购物篮辅助表')
)
RETURN
IF(
[重复的产品排除] || ISEMPTY(CustomersA) || ISEMPTY(CustomersB),
BLANK(),
COUNTROWS(INTERSECT(CustomersA, CustomersB))
)
公式逻辑解析：
变量1：VAR CustomersA = CALCULATETABLE(...)
定义购买'购物篮切片表'中产品的客户集合
SUMMARIZE('销售', '销售'[客户关键字])：从销售表中提取不重复的客户关键字
USERELATIONSHIP('购物篮切片表'[产品], '销售'[产品])：临时激活这两个表之间的产品关系
ALL('购物篮辅助表')：移除对'购物篮辅助表'的所有筛选
变量2：VAR CustomersB = CALCULATETABLE(...)
定义购买'购物篮辅助表'中产品的客户集合
SUMMARIZE('销售', '销售'[客户关键字])：同样提取不重复的客户关键字
KEEPFILTERS('购物篮辅助表')：保持对'购物篮辅助表'的现有筛选条件
返回结果：
IF([重复的产品排除] || ISEMPTY(CustomersA) || ISEMPTY(CustomersB), BLANK(), COUNTROWS(INTERSECT(CustomersA, CustomersB)))
条件判断部分：
[重复的产品排除]：如果为真则返回空值
ISEMPTY(CustomersA)：如果CustomersA为空集合
ISEMPTY(CustomersB)：如果CustomersB为空集合
以上任一条件为真则返回BLANK()
计算部分：
INTERSECT(CustomersA, CustomersB)：获取两个客户集合的交集
COUNTROWS()：计算交集中的行数(即客户数量)
重点高阶公式：
INTERSECT：集合运算，获取两个表的交集
ISEMPTY：检查表是否为空，用于错误处理
公式：
支持度 = 同时购买A和B的订单数 / 总客户数
支持度 =
IF(
[重复的产品排除] , BLANK() ,
DIVIDE([关联客户数],CALCULATE([客户数量],ALL()
)))
公式逻辑解析：
首先检查是否满足"[重复的产品排除]"条件
如果为真，则返回空值(BLANK())
如果为假，则计算支持度
支持度的计算方法是：关联客户数除以总客户数(使用ALL()移除了所有筛选上下文)
公式：
提升度 = 支持度 / [（购买A的次数/总购买订单数） * （购买B的次数/总购买订单数）]
提升度 = VAR A=DIVIDE([bayA类产品的客户数],CALCULATE([客户数量],ALL()))
VAR B=DIVIDE([bayB类产品的客户数],CALCULATE([客户数量],ALL()))
VAR C=A*B
RETURN
DIVIDE([支持度],C)
公式逻辑解析：
计算单个产品的购买概率
变量1：
A = bayA类产品的客户数 / 总客户数
→ 购买bayA类产品的客户比例（即 P(A)）
变量2：
B = bayB类产品的客户数 / 总客户数
→ 购买bayB类产品的客户比例（即 P(B)）
计算独立事件预期概率
变量3：
C = A * B
→ 如果A和B独立无关，两者同时被购买的理论概率（即 P(A) × P(B)）
计算提升度
提升度 = 支持度 / C
→ 实际共现概率与独立概率的比值
公式：
置信度 = 同时购买A和B的订单数 / 购买A的订单数
置信度 = DIVIDE([关联客户数],[bayA类产品的客户数])
公式逻辑解析：
这个公式计算的是置信度指标，其逻辑是：
分子：[关联客户数] - 同时购买两种产品的客户数量
分母：[bayA类产品的客户数] - 购买A类产品的客户总数
计算：用关联客户数除以A类产品的客户总数
公式：
交叉销售潜力指数 = 提升度(Lift) × 关联销售贡献率
关联销售贡献率 = 关联销售额 / 主产品销售额
交叉销售潜力指数 =
[提升度] * DIVIDE(
[关联销售额],
[bayA类产品的销售额],
0
)
公式逻辑解析：
[提升度]
这是一个预先定义的度量值，通常表示产品组合出现的频率与各自独立出现频率的比值
在关联规则分析中，提升度（Lift）大于1表示正相关，等于1表示独立，小于1表示负相关
DIVIDE函数
计算两个度量值的比值，提供安全除法功能（避免除零错误）
结构：DIVIDE（分子， 分母， 替代值）
这里替代值为0，表示当分母为0时返回0
[关联销售额]
是另一个预先定义的度量值，表示与主产品（bayA类）相关联的销售额
通常计算的是客户购买主产品后，又购买其他关联产品的总销售额
[bayA类产品的销售额]
这是主产品类别的总销售额，作为基准值
公式：
推荐优先级 =
VAR LiftValue = [提升度]
VAR SalesRatio = DIVIDE( [关联销售额],[bayA类产品的销售额], 0)
RETURN
SWITCH(
TRUE(),
ISBLANK(LiftValue) || ISBLANK(SalesRatio), BLANK(),
LiftValue > 1&& SalesRatio > 0.1, "高优先级",
LiftValue > 1 && SalesRatio > 0.05, "中优先级",
LiftValue > 1, "低优先级",
"不推荐"
)
公式逻辑解析：
变量定义
LiftValue: 存储提升度值
SalesRatio: 计算关联销售额与主产品销售额的比值
SWITCH逻辑
使用SWITCH(TRUE(),...)模式实现多条件判断
将判断条件按照优先级从高到低依次排列
优先级分类标准
高优先级： Lift>1 且 销售比例>10%
中优先级： Lift>1 且 销售比例>5%
低优先级： Lift>1 但 销售比例≤5%
不推荐： Lift≤1
行业应用建议
零售业：
高优先级组合适合做促销套装
中优先级适合做关联陈列
低优先级可做线上推荐
电商平台：
高优先级： "买了也买"首位推荐
优先级： 购物车推荐
低优先级： 商品详情页推荐
★制作可视化报告
第一步：设置报表页格式选项，选择画布背景，上传我们设计好的素材
第二步：插入一个矩形图，填充颜色，打开阴影、发光功能
第三步：添加主页图标图像，启用交互功能，设置类型为书签，并指定书签为主页，点击该图标即可快速跳转至主页。
第四步：插入图像导航图标。
第五步：插入两个切片器，第一个切片器拖入日期表年度季度字段，第二个切片器拖入购物篮切片表子类别字段。
第六步：插入一个矩阵图，进行拖入购物篮辅助表子类别字段，值拖入度量值推荐优先级、bayA类产品的客户数、bayA类产品的销售额、bayB类产品的客户数、bayB类产品的销售额、关联客户数、关联销售额、交叉销售潜力指数、支持度、提升度、置信度。
第七步：插入一个散点图，值拖入购物篮辅助表子类别字段，X轴拖入度量值提升度，Y轴拖入度量值支持度，图例拖入购物篮辅助表分类字段，大小拖入度量值置信度，工具提示拖入度量值推荐优先级，向视觉对象添加进一步分析， 分别给支持度、提升度添加一条中值线。
第八步：如图美化页面。好的，今天的讲解就到这里。后期课程也会逐渐增加难度，如果在学习过程中需要帮助，欢迎随时联系作者，精彩内容，敬请期待。
获取模板文件或咨询，联系瓶子微信wdfyqx | 
