PowerBI企业运营分析——线性回归销售预测
欢迎来到Powerbi小课堂，在竞争激烈的市场环境中，企业运营分析平台成为提升竞争力的核心工具。
该平台通过整合多源数据，实现关键指标的实时监控，从而迅速洞察业务动态，精准识别问题与潜在机遇。其可视化看板和智能预警功能，帮助管理者直观掌握运营动态，优化资源分配；而深度分析模块则能挖掘潜在趋势，支撑战略决策。无论是降本增效、风险规避，还是市场拓展，该平台均能提供坚实的数据基础，助力企业实现精细化运营，稳步迈向可持续发展之路。
本期课程我们依然将重点放到整合前期分散知识点，帮助您建立清晰的模板搭建思路，本节课程涵盖业绩预测模板制作，本节课程核心为线性回归算法，一起接受挑战吧。
图片
想要一份这样的可视化看板吗？想学吗？我教你呀！
图片
一、案例背景：
我司作为国内一家大型服装电商企业，业务范围涵盖男装、女装、童装和配饰。当前，我们正致力于开发企业运营分析系统，旨在通过技术手段实现业绩提升、指标监控、绩效考核、客户维护开发和企业发展预测，促进数据共享与流程优化，进而提升决策效率与运营效率，优化资源配置，强化风险控制，为企业的可持续发展注入动力。
二、设计思路：
借助财务、产品、客户分析及盈亏预测，精确把握企业发展脉络，构建科学合理的考核机制，同时有效控制变动成本，进而提升企业盈利能力。
（1）、准备基础数据
（2）、数据清洗
（3）、建立关系视图
（4）、个性美化设计
（5）、数据建模（度量值）
（6）、制作可视化报告
三、数据建模
辅助表建立 公式：
预测辅助表 = // 最小二乘法SELECTCOLUMNS( UNION(ADDCOLUMNS( GENERATESERIES( 2021 , 2024 ) , -- AC 部分"Type" , "AC"),ADDCOLUMNS( GENERATESERIES( 2025 , 2029 ) , -- FC 部分"Type" , "FC" )),"X" , [Value] , "Type" , [Type])公式解析：
创建一个预测辅助表，结合了实际数据(AC)和预测数据(FC)
GENERATESERIES(2021, 2024) - 生成一个从2021到2024的序列(实际数据年份)
ADDCOLUMNS(..., "Type", "AC") - 为这些年份添加一个"Type"列，值为"AC"(表示实际数据)
GENERATESERIES(2025, 2029) - 生成一个从2025到2029的序列(预测数据年份)
ADDCOLUMNS(..., "Type", "FC") - 为这些年份添加一个"Type"列，值为"FC"(表示预测数据)
UNION(...) - 将实际数据部分和预测数据部分合并为一个表
SELECTCOLUMNS(...) - 选择并重命名列，最终输出两列：
"X"列：年份值(2021-2029)
"Type"列：标识是实际数据("AC")还是预测数据("FC")
公式：
销售金额-万元 = DIVIDE( [销售金额] , 10000 )销售利润-万元 = DIVIDE( [销售利润] , 10000 )公式解析：
用于将销售数据从原始单位转换为以"万元"为单位的数值
公式：
预测销售金额 = VARX_Current = SELECTEDVALUE('预测辅助表'[X])VARAreaType = SELECTEDVALUE('预测辅助表'[Type])// 当前年份的实际值计算VARY_Current = CALCULATE([销售金额-万元],TREATAS({X_Current}, '日期表'[年份]))// 获取已知历史数据VARKnownData = FILTER(ADDCOLUMNS(CALCULATETABLE(VALUES('预测辅助表'[X]),'预测辅助表'[Type] = "AC",ALLSELECTED('预测辅助表')),"KnownY", CALCULATE([销售金额-万元],TREATAS({'预测辅助表'[X]}, '日期表'[年份]) )),NOT(ISBLANK('预测辅助表'[X])) && NOT(ISBLANK([KnownY])))// 线性回归计算VARCountItems = COUNTROWS(KnownData)VARSumX1 = SUMX(KnownData, '预测辅助表'[X])VARSumX2 = SUMX(KnownData, '预测辅助表'[X] ^ 2)VARSumY = SUMX(KnownData, [KnownY])VARSumXY = SUMX(KnownData, '预测辅助表'[X] * [KnownY])VARAvgX = AVERAGEX(KnownData, '预测辅助表'[X])VARAvgY = AVERAGEX(KnownData, [KnownY])VARSlope = DIVIDE(CountItems * SumXY - SumX1 * SumY,CountItems * SumX2 - SumX1 ^ 2)VARIntercept = AvgY - Slope * AvgX// 结果返回RETURNIF(AreaType = "AC",Y_Current,Intercept + Slope * X_Current)公式解析：
该DAX公式用于计算销售金额的预测值，结合了当前实际值和基于历史数据的线性回归预测。主要逻辑如下：
如果是"AC"区域，直接返回当前年份的实际销售金额（历史数据）。
如果是其他区域，使用线性回归模型预测未来销售金额。
\2. 变量定义
(1) X_Current（当前年份值）
作用：获取当前筛选上下文中的年份值（如2020、2021等）。
SELECTEDVALUE：返回当前筛选器下的唯一值，如果有多值则返回空值。
(2) AreaType（区域类型）
作用：获取当前筛选上下文中的区域类型（如"AC"、"BD"等）。
用途：决定是返回实际值还是预测值。
- 1. 当前年份实际值计算（Y_Current）
作用：计算当前年份的实际销售金额。
CALCULATE：在特定筛选条件下计算度量值。
TREATAS：将X_Current（年份）转换为日期表的筛选器，确保正确匹配。
4.获取历史数据（KnownData）
分解步骤：
CALCULATETABLE(VALUES('预测辅助表'[X]), ...)
获取所有"AC"区域的年份值（X列）。
ALLSELECTED('预测辅助表')：保留外部筛选上下文，但强制Type = "AC"。
ADDCOLUMNS(..., "KnownY", ...)
为每一年份添加对应的实际销售金额列KnownY。
TREATAS({'预测辅助表'[X]}, '日期表'[年份])：确保年份匹配正确。
FILTER(..., NOT(ISBLANK(...)))
过滤掉X或KnownY为空的数据，确保回归计算的数据完整性。
- 1. 线性回归计算
(1) 计算回归统计量
CountItems = COUNTROWS(KnownData)
作用：计算用于回归分析的有效数据点数量
数学表示：n
重要性：影响回归系数的计算精度（数据点越多，回归越可靠）
用于后续所有分母计算
示例：如果有2018-2022共5年的数据，CountItems = 5
SumX1 = SUMX(KnownData, '预测辅助表'[X])
作用：计算所有X值（年份）的总和
数学表示：ΣX
计算方式：SumX1=X1+X2+...+Xn
示例：如果X值为[2018,2019,2020,2021,2022]，则SumX1 = 10090
SumX2 = SUMX(KnownData, '预测辅助表'[X] ^ 2)
作用：计算所有X值的平方和
数学表示：ΣX²
计算方式：SumX2=X12+X22+...+Xn2
示例：继续上例，SumX2 = 2018² + 2019² + ... + 2022² = 4,072,724 + ... + 4,088,484
重要性：用于计算X的变异量（分母部分）
SumY = SUMX(KnownData, [KnownY])
作用：计算所有Y值（销售金额）的总和
数学表示：ΣY
计算方式：SumY=Y1+Y2+...+Yn
业务意义：历史销售总额，反映整体规模
SumXY = SUMX(KnownData, '预测辅助表'[X] * [KnownY])
作用：计算X与Y的乘积和
数学表示：ΣXY
计算方式：SumXY=X1Y1+X2Y2+...+Xn**Yn
重要性：分子部分的关键变量，反映X和Y的共同变化趋势
示例：如果2020年销售500万，则该项含2020 * 500
AvgX = AVERAGEX(KnownData, '预测辅助表'[X])
作用：计算X值的平均值
数学表示：X̄
计算方式：AvgX=nΣ/X
示例：(2018+2019+...+2022)/5 = 2020
AvgY = AVERAGEX(KnownData, [KnownY])
作用：计算Y值的平均值
数学表示：Ȳ
计算方式：AvgY=nΣ/Y
业务意义：历史平均销售水平
(2) 计算斜率（Slope）
公式：
图片
作用：计算回归线的斜率（每增加一年，销售金额的变化趋势）。
(3) 计算截距（Intercept）
公式：
图片
作用：计算回归线的截距（基准值）。
\6. 结果返回
逻辑：
如果AreaType = "AC"，直接返回当前年份的实际销售金额（Y_Current）。
否则，使用回归方程 Y = Intercept + Slope * X 预测未来销售金额。
图片
公式：
预测销售利润 = VARX_Current = SELECTEDVALUE('预测辅助表'[X])VARAreaType = SELECTEDVALUE('预测辅助表'[Type])// 当前年份的实际利润计算VARY_Current = CALCULATE([销售利润-万元],TREATAS({X_Current}, '日期表'[年份]))// 获取已知历史利润数据（优化后的版本）VARKnownData = FILTER(ADDCOLUMNS(CALCULATETABLE(VALUES('预测辅助表'[X]),'预测辅助表'[Type] = "AC",ALLSELECTED('预测辅助表')),"KnownY", CALCULATE([销售利润-万元],TREATAS({'预测辅助表'[X]}, '日期表'[年份]) )),NOT(ISBLANK('预测辅助表'[X])) && NOT(ISBLANK([KnownY])))// 线性回归计算（优化计算效率）VARCountItems = COUNTROWS(KnownData)VARSumX1 = SUMX(KnownData, '预测辅助表'[X])VARSumX2 = SUMX(KnownData, '预测辅助表'[X] ^ 2)VARSumY = SUMX(KnownData, [KnownY])VARSumXY = SUMX(KnownData, '预测辅助表'[X] * [KnownY])VARAvgX = AVERAGEX(KnownData, '预测辅助表'[X])VARAvgY = AVERAGEX(KnownData, [KnownY])VARSlope = DIVIDE(CountItems * SumXY - SumX1 * SumY,CountItems * SumX2 - SumX1 ^ 2,0 // 添加默认值防止除零错误)VARIntercept = AvgY - Slope * AvgX// 结果返回（添加了错误处理）RETURNIF(AreaType = "AC",Y_Current,IF(ISBLANK(Slope) || ISBLANK(Intercept),BLANK(), // 如果无法计算回归参数则返回空白Intercept + Slope * X_Current ))公式解析：
以上公式与预测销售金额的逻辑基本相同，但是做了部分优化，计算相对健壮
1.变量定义
X_Current：获取当前行的年份值（X轴值）
AreaType：获取当前行的类型（"AC"表示实际值，"FC"表示预测值）
2.当前年份实际利润计算
计算当前年份的实际销售利润
TREATAS函数将X_Current的值转换为筛选上下文，应用于日期表的年份列
3.获取已知历史数据（优化版）
获取所有类型为"AC"（实际值）的历史数据
为每条记录添加对应的销售利润值（KnownY），过滤掉X或Y为空的记录
4.线性回归计算
4.1基础统计量计算
计算数据点数量、X总和、X平方和、Y总和、XY乘积和、X平均值、Y平均值
4.2斜率计算
使用最小二乘法计算回归线斜率
公式：斜率 = (nΣXY - ΣXΣY) / (nΣX² - (ΣX)²)
添加了除零保护（如果分母为零则返回0）
4.3截距计算
计算回归线截距
公式：截距 = Y平均值 - 斜率 * X平均值
5.结果返回（带错误处理）
如果当前行类型是"AC"（实际值），返回当前年份的实际利润
如果是预测值：
首先检查斜率或截距是否为空白（计算失败）
如果计算失败，返回空白
否则使用回归方程计算预测值：Y = 截距 + 斜率 * X
公式：
预测销售数量 = VARX_Current = SELECTEDVALUE('预测辅助表'[X])VARAreaType = SELECTEDVALUE('预测辅助表'[Type])// 当前年份的实际销售数量VARY_Current = CALCULATE([销售数量],TREATAS({X_Current}, '日期表'[年份]))// 获取已知历史销售数据VARKnownData = FILTER(SUMMARIZE(CALCULATETABLE('预测辅助表','预测辅助表'[Type] = "AC",ALLSELECTED('预测辅助表')),'预测辅助表'[X],"KnownY", CALCULATE([销售数量],TREATAS({'预测辅助表'[X]}, '日期表'[年份]) )),NOT(ISBLANK('预测辅助表'[X])) && NOT(ISBLANK([KnownY])))// 计算回归参数VARCountItems = COUNTROWS(KnownData)VARSumX1 = SUMX(KnownData, '预测辅助表'[X])VARSumX2 = SUMX(KnownData, '预测辅助表'[X] ^ 2)VARSumY = SUMX(KnownData, [KnownY])VARSumXY = SUMX(KnownData, '预测辅助表'[X] * [KnownY])VARSlope = IF(CountItems >= 2,DIVIDE(CountItems * SumXY - SumX1 * SumY,CountItems * SumX2 - SumX1 ^ 2, 0), 0)VARIntercept = IF(CountItems >= 2,AVERAGEX(KnownData, [KnownY]) - Slope * AVERAGEX(KnownData, '预测辅助表'[X]), 0)// 计算预测值VARPredictedValue = IF(AreaType = "AC",_Current,MAX(0, Intercept + Slope * X_Current) // 确保预测值不小于0)RETURNPredictedValue公式解析：
\1. 变量定义
I_Current：获取当前行的年份值（X轴值）
AreaType：获取当前行的类型（"AC"表示实际值，"FC"表示预测值）
2.当前年份实际销售数量计算
计算当前年份的实际销售数量
TREATAS函数将X_Current的值转换为筛选上下文，应用于日期表的年份列
3.获取已知历史数据
获取所有类型为"AC"（实际值）的历史数据
使用SUMMARIZE创建包含年份和对应销售数量的表
过滤掉X或Y为空的记录
4.线性回归计算
4.1 基础统计量计算
计算数据点数量、X总和、X平方和、Y总和、XY乘积和
4.2 斜率计算（带数据量检查）
使用最小二乘法计算回归线斜率
添加数据点数量检查（至少需要2个点才能计算斜率）
4.3 截距计算（带数据量检查）
计算回归线截距
同样添加数据点数量检查
5.预测值计算与返回
如果当前行类型是"AC"（实际值），返回当前年份的实际销售数量
如果是预测值：
使用回归方程计算预测值：Y = 截距 + 斜率 * X
使用MAX(0,...)确保预测值不小于0（销售数量不能为负）
返回最终结果
公式：
销售金额2025年 = VARA = CALCULATETABLE( ADDCOLUMNS(SUMMARIZE( ALLSELECTED( '预测辅助表' ) , '预测辅助表'[X] ) ,"T[FCFC]" , [预测销售金额] ), ALL( '日期表') )VARB = MAXX( FILTER( A , [X] = 2025 ) , T[FCFC] )RETURNB销售利润2025年 = VARA = CALCULATETABLE( ADDCOLUMNS(SUMMARIZE( ALLSELECTED( '预测辅助表' ) , '预测辅助表'[X] ) ,"T[FCFC]" , [预测销售利润] ), ALL( '日期表') )VARB = MAXX( FILTER( A , [X] = 2025 ) , T[FCFC] )RETURNB销售数量2025年 = VARA = CALCULATETABLE( ADDCOLUMNS(SUMMARIZE( ALLSELECTED( '预测辅助表' ) , '预测辅助表'[X] ) ,"T[FCFC]" , [预测销售数量] ), ALL( '日期表') )VARB = MAXX( FILTER( A , [X] = 2025 ) , T[FCFC] )RETURNB公式解析：
以销售数量2025年为例
1.创建包含所有年份和预测值的表（变量A）
SUMMARIZE(ALLSELECTED('预测辅助表'), '预测辅助表'[X])：创建一个包含所有选定年份值的单列表
ADDCOLUMNS：为这个表添加一个新列"T[FCFC]"，其值为[预测销售数量]度量值
CALCULATETABLE与ALL('日期表')：确保计算时不考虑日期表上的任何筛选器影响
2.筛选出2025年的数据并获取预测值（变量B）
FILTER(A, [X] = 2025)：从上表A中筛选出2025年的行
MAXX：从筛选结果中提取最大的预测值（如果有多条记录）
3.返回结果
返回变量B的值，即2025年的预测销售数量
公式：
销售金额-增长预测 = [销售金额2025年] - [销售金额-万元]
销售利润-增长预测 = [销售利润2025年] - [销售利润-万元]
销售数量-增长预测 = [销售数量2025年] - [销售数量]
公式解析：计算预测值与当前值的差额
公式解析：
SELECTEDVALUE('预测辅助表'[Type])
获取当前上下文中'预测辅助表'[Type]字段的值
SELECTEDVALUE函数返回当列中只有一个唯一值时的该值
条件判断(IF函数)
判断Type字段的值是否为"AC"
如果是"AC"，返回红色("#FF0000")
如果不是"AC"(即其他值，如"FC")，返回绿色("#00FF00")
公式：销售金额-增长预测 = [销售金额2025年] - [销售金额-万元]销售利润-增长预测 = [销售利润2025年] - [销售利润-万元]销售数量-增长预测 = [销售数量2025年] - [销售数量]四、可视化报告制作
1、业绩概览制作
首先，配置报表页面的格式选项，选定画布背景，并上传预先设计好的素材文件。
第二步：插入一个图像，AI机器人图标，点开操作功能，给此图像添加登录页书签。
接着，添加一个卡片图表，并将度量值中的日历字段拖放到切片器控件中。
第四步：插入七个书签按钮，输入数据中心、产品维度、客户开发、业绩分析、地域分析、业绩考核、表格维度，并给书签按钮添加我们设计好的图标。
然后，插入一个形状元素，作为之前创建的视觉对象的背景装饰。
第六步：插入四个切片器，将维度-销售人员表销售经理、销售人员拖入字段中，将维度-产品表类别、子类别拖入字段中，将维度-省份大区表地区、省份拖入字段中，将维度-客户表行业、职业拖入字段中。
第七步：插入四个矩阵图，行分别拖入维度-销售人员表销售经理、销售人员拖入字段中，维度-产品表类别、子类别拖入字段中，维度-省份大区表地区、省份拖入字段中，维度-客户表行业、职业字段，值拖入度量值销售数量（重命名为2024年销量）、销售数量2025年（重命名为2025年销量）、销售数量-增长预测（重命名为销量增长预测）、销售金额-万元（重命名为2024年业绩）、销售金额2025年（重命名为2025年业绩）、销售金额-增长预测（重命名为业绩增长预测）、销售利润-万元（重命名为2024年利润）、销售利润2025年（重命名为2025年利润）、销售利润-增长预测（重命名为利润增长预测），打开视觉对象筛选器，将年份维度表年份字段用于视觉对象筛选器，筛选类型选择高级筛选器，显示值等于2024，应用此筛选器，将四个矩阵图叠放备用。
第八步：插入四个空白按钮，形状选择剪裁选项卡，右上角，文本分别输入客户维度预测、地域维度预测、地域维度预测和销售维度预测，再插入两个空白按钮，形状选择药丸，文本分别输入打开筛选、关闭筛选，以上按钮备用。
第九步：创建三个簇状柱形图，将预测辅助表的X字段拖至X轴，再将预测销售数量、预测销售金额、预测销售利润三个度量值分别拖至Y轴。接着，配置视觉对象格式，启用数据标签，并设置列的颜色，采用度量值预测KPI的颜色进行动态配色。
第十步：在视图选项卡中启用选择功能，选中新建的按钮控件和切片器，通过右键操作将它们分组并重命名为‘指标切换组’。随后，选中四个矩阵图，同样通过右键操作分组并重命名为‘预测矩阵’。
第十一步：在视图选项卡中启用书签功能，对‘指标切换组’进行配置，隐藏所有切片器并关闭筛选按钮；对‘预测矩阵’进行配置，隐藏除‘行业职业矩阵’外的所有预测矩阵。然后，添加一个书签并重命名为‘客户行业矩阵’。重复上述隐藏和显示矩阵图的步骤，依次添加‘产品类别矩阵’、‘地区省份矩阵’、‘销售人员矩阵’三个书签。最后，再插入一个书签并重命名为‘关闭筛选器’。
第十二步：视图选项卡，打开书签功能，指标切换组隐藏所有剪裁选项卡按钮控件和打开筛选按钮，显示所有切片器和关闭筛选按钮，再插入一个书签，重命名为打开筛选器，选中新建的书签，右键选择分组，重命名为页内切换业绩预测，点击以上按钮控件，打开操作功能，将对应书签连接到按钮控件。
第十三步：如图美化界面
第十四步：进入视图窗口，启用书签功能，并为当前页面创建一个书签。
第十五步：在数据中心页面，找到并点击标有'业绩预测'的按钮，进入操作界面，选择类型为'书签'，然后将之前创建的书签添加到该按钮上。
好的，今天的讲解就到这里。后期课程也会逐渐增加难度，精彩内容，敬请期待。
