PowerBI企业运营分析——线性回归销售预测

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. 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. 线性回归计算

(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的颜色进行动态配色。

第十步：在视图选项卡中启用选择功能，选中新建的按钮控件和切片器，通过右键操作将它们分组并重命名为‘指标切换组’。随后，选中四个矩阵图，同样通过右键操作分组并重命名为‘预测矩阵’。

第十一步：在视图选项卡中启用书签功能，对‘指标切换组’进行配置，隐藏所有切片器并关闭筛选按钮；对‘预测矩阵’进行配置，隐藏除‘行业职业矩阵’外的所有预测矩阵。然后，添加一个书签并重命名为‘客户行业矩阵’。重复上述隐藏和显示矩阵图的步骤，依次添加‘产品类别矩阵’、‘地区省份矩阵’、‘销售人员矩阵’三个书签。最后，再插入一个书签并重命名为‘关闭筛选器’。

第十二步：视图选项卡，打开书签功能，指标切换组隐藏所有剪裁选项卡按钮控件和打开筛选按钮，显示所有切片器和关闭筛选按钮，再插入一个书签，重命名为打开筛选器，选中新建的书签，右键选择分组，重命名为页内切换业绩预测，点击以上按钮控件，打开操作功能，将对应书签连接到按钮控件。

第十三步：如图美化界面

第十四步：进入视图窗口，启用书签功能，并为当前页面创建一个书签。

第十五步：在数据中心页面，找到并点击标有'业绩预测'的按钮，进入操作界面，选择类型为'书签'，然后将之前创建的书签添加到该按钮上。

好的，今天的讲解就到这里。后期课程也会逐渐增加难度，精彩内容，敬请期待。