采购BI实践

关注

原创 2025-11-19 23:44:41 799 阅读 AI 写同款· GEO 优化 ›

基于Power BI Desktop的电子产品综合数据分析案例 1、项目简介 2、数据理解表1：2020年各省人口数量表：地区：各个省份人口数：2020年各个省份的人口数表2：电子产品销售表； event_time：下单时间，包含其他内容，需要清洗； order_id：订单编号 product_id:产品标号 category_id :类别编号，存在空值； category_code :类别，存在空值； brand :品牌 price :价格 user_id :用户编号 age :年龄 sex :性别 local:省份 buy_cnt:购买数量阅读详情

该下仅方法论自拟，其余全程使用ai！

01价格分析BI

0101主体

需求构建一个：

①能够系统分析出采购的重要物料品类、重要供应商

②分析各物料品类的平均价格，价格稳定性及价格趋势

③能区分不同供应商分别在物料上给付的最优、最劣价格

基于以上需求，提供一个应具备如下功能的看板：

①日期及物料类型切片器，其中物料类型选择时只能选择1个。

②受切片器影响的对应特定物料的采购供应商总金额排行（显示供应商名，列的直方图，确切数值，以及供应商数量，并将占据该物料类型90%金额的供应商标红）

③显示当前切片器下采购的总金额

④存货集成表，显示存货编码，供应数量，计量单位，消耗金额，最低采购单价，最低采购单价物料的数量占比，最高采购单价，最高采购单价的数量占比，单价（高低）差异率，平均单价，最新价格。

⑤采购供应商散点图，纵轴是供应商在存货集成表（存货编码）及切片器筛选下的总金额，横轴是其单价，其中的散点图是对应的供应商。

⑥时序显示的选中物料和存货编码的物料单价趋势折线图（附有线性趋势虚线），横轴是时间，纵轴是对应时间该物料的采购单价。

0102构建的报表结构

首先创建：物料表、供应商表、采购订单表（实践可能不止，但大致方向一致）

载入powerbi中，一步步实现前述功能。（默认已完成清洗，金额单位、币种置换等）

①日期及物料类型切片器

日期表直接建立（采购订单表[采购日期]）

然后创建三个度量值：

选中物料金额 = 
CALCULATE(
    SUM('采购订单表'[金额])
)

总金额 = 
CALCULATE(
    SUM('采购订单表'[金额]),
    REMOVEFILTERS('物料表')
)

选中物料占比 = 
DIVIDE(
    [选中物料金额],
    [总金额]
)

然后在切片器中添加物料表[物料类型]，在卡片图中添加“选中物料金额”和“选中物料占比”。

图示：

②采购供应商总金额排行

首先创建度量值：

供应商总金额 = 
SUM('采购订单表'[金额])

供应商数量 = 
DISTINCTCOUNT('采购订单表'[供应商编码])

前90%供应商数量 = 
VAR TotalAmount = CALCULATE([总金额], ALLSELECTED('供应商表'))
VAR SuppliersTable =
    ADDCOLUMNS(
        VALUES('供应商表'[供应商编码]),
        "SupplierAmount", [供应商总金额]
    )
VAR SortedSuppliers =
    FILTER(
        SuppliersTable,
        [SupplierAmount] > 0
    )
VAR RankedSuppliers =
    ADDCOLUMNS(
        SortedSuppliers,
        "Rank", RANKX(SortedSuppliers, [SupplierAmount], , DESC)
    )
VAR CumulativeTable =
    ADDCOLUMNS(
        RankedSuppliers,
        "CumulativeAmount",
            VAR CurrentRank = [Rank]
            RETURN
                SUMX(
                    FILTER(RankedSuppliers, [Rank] <= CurrentRank),
                    [SupplierAmount]
                )
    )
VAR Threshold = TotalAmount * 0.9
VAR SuppliersInTop90 =
    FILTER(
        CumulativeTable,
        [CumulativeAmount] <= Threshold
    )
RETURN
    IF(
        ISEMPTY(SuppliersInTop90),
        0,
        MAXX(SuppliersInTop90, [Rank])
    )

使用簇状条形图，添加：

Y轴：供应商表[供应商名称]

X轴：供应商总金额

使用卡片图，添加：供应商数量、前90%供应商数量。

图示：

③当前切片器下采购的总金额

创建度量值：

采购总金额 = 
CALCULATE(
    SUM('采购订单表'[金额]),
    ALL('物料表')
)

采购下单次数 = 
CALCULATE(
    DISTINCTCOUNT('采购订单表'[订单编码]),
    ALL('物料表')
)

放入卡片即可。

④存货集成表

创建以下度量值：

供应数量 = 
SUM('采购订单表'[采购数量])

消耗金额 = 
SUM('采购订单表'[金额])

最低采购单价 = 
MIN('采购订单表'[采购单价])

最高采购单价 = 
MAX('采购订单表'[采购单价])

最低价数量占比 = 
VAR MinPrice = [最低采购单价]
VAR MinPriceQty = 
    CALCULATE(
        [供应数量],
        '采购订单表'[采购单价] = MinPrice
    )
RETURN
DIVIDE(MinPriceQty, [供应数量])

最高价数量占比 = 
VAR MaxPrice = [最高采购单价]
VAR MaxPriceQty = 
    CALCULATE(
        [供应数量],
        '采购订单表'[采购单价] = MaxPrice
    )
RETURN
DIVIDE(MaxPriceQty, [供应数量])

单价差异率 = 
DIVIDE(
    [最高采购单价] - [最低采购单价],
    [最低采购单价]
)

平均单价 = 
DIVIDE(
    [消耗金额],
    [供应数量]
)

最新价格 = 
CALCULATE(
    MAX('采购订单表'[采购单价]),
    TOPN(
        1,
        '采购订单表',
        '采购订单表'[采购日期],
        DESC
    )
)

将度量值全部添加进表的列即可。

图示：

⑤采购供应商散点图

创建度量值：

供应商加权平均单价 = 
DIVIDE(
    [供应商总金额],
    SUM('采购订单表'[采购数量])
)

供应商采购频次 = 
DISTINCTCOUNT('采购订单表'[订单编码])

散点图中：

X轴：供应商加权平均单价

Y轴：供应商总金额

图例：供应商表[供应商名称]

大小：供应商采购频次

图示：

⑥物料单价趋势折线图

创建度量值：

按日平均单价 = 
CALCULATE(
    AVERAGE('采购订单表'[采购单价]),
    VALUES('采购订单表'[采购日期])
)

连续日期序列 = 
VAR StartDate = MIN('采购订单表'[采购日期])
VAR EndDate = MAX('采购订单表'[采购日期])
RETURN
CALENDAR(StartDate, EndDate)

创建表：

连续日期表 = 
VAR MinDate = MIN('采购订单表'[采购日期])
VAR MaxDate = MAX('采购订单表'[采购日期])
RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "年", YEAR([Date]),
    "月", FORMAT([Date], "MM"),
    "日", DAY([Date]),
    "年月", FORMAT([Date], "YYYY-MM")
)

建立连续日期表[Date]与采购订单表[采购日期]，设置多对一并且单向的关系（无空白）。

折线图：

X轴：连续日期表[Date]

Y轴：按日平均单价

图示：

加入更多数据：

完成！

0103报表的使用

”物料类型“可以根据实际需要转换为产品品类或其他的类型字段。这里就是可以唯一判断一种关键类型信息的：供应商及价格趋势。

在供应商上，可以看出该类型在公司中的重要性（点1），供货供应商的数量（点2），是否有重要垄断型供应特征还是分散型供应特征（可以从散点【向上是采购金额，向右是采购单价，大小是采购数额】上看出，按道理来说采购金额越高（越靠上，散点越大），因为同一种品类，越左上可能就越重要，右上可能就需要一个重组或谈判的操作，右下可能就是需要去除的。

在存货上，实际上可以看不同存货类型的最高价和最低价的差异率（波动指标）。

在整体趋势上，不仅可以看到单价趋势【向上和向下都可以做归因分析】，也可以看到趋势的波动性【对于单价有高波动的，可适当增加安全库存或者优化，我们喜欢稳定可预测性】。

02成本分析BI

0201主体

需求构建一个：

①系统分析当期各产品当前产量、具体生产成本及其同比。

②分析当期产量对应品类分类、以及成本结构。

③分析全时产量及成本趋势。

基于以上需求，提供一个应具备如下功能的看板：

①日期切片器：可下拉选中一个日期【格式：年-月（2024-1、20242...）】，然后根据该切片器输出当期和环比的总产量、总生产成本、单位平均生产成本及显示对应增减百分比。

②存货表格：显示对应存货名称，及其按顺序排列的存货产量柱，以及其单位生产成本，并在产量和单位生产成本标注环比的增减。【后续进行特定选择时，还能据此分析其成本结构，存货分类的产品品类产量，以及和产量和单位生产成本趋势。】

③产品品类产量柱状图：如名。

④成本结构环状图：将筛选特定存货或特定产品品类，输出其对应的成本结构（若有更多细分【例如一级细分为材料、制造、人工，二级细分就是“人工”中的直接人工，管理人员工资等等】可以根据需要架设更多细分出来的成本结构环状图）

⑤产量成本趋势图：显示特定选择的存货、产品品类的：产量、成本趋势（不记日期筛选，近一年）折线图。

0202所需的主要数据

首先让ai根据一般流程，生成对应的制造业成本分析可能需要的表：

产品主数据表（存货编码，存货名称，产品品类，是否启用）

生产成本明细表（生产日期，存货编码，产量，总生产成本，材料成本，人工成本，制造费用，其他成本）

成本结构明细表（生产日期，存货编码，成本类型，成本子类型，成本金额）

0203构建的报表结构

首先创建：产品主数据表、生产成本明细表、成本结构明细表（实践可能不止，但大致方向一致）

①日期表构建/日期切片器

新建日期表：

DateTable =

VAR MinDate = MIN('生产成本明细表'[生产日期])

VAR MaxDate = MAX('生产成本明细表'[生产日期])

RETURN

ADDCOLUMNS (

CALENDAR (MinDate, MaxDate),

"Year", YEAR([Date]),

"Month", MONTH([Date]),

"YearMonth", FORMAT([Date], "YYYY年M月"), -- 改为中文格式更清晰

"YearMonthShort", FORMAT([Date], "YYYY-M"), -- 短格式

"YearMonthNum", YEAR([Date]) * 100 + MONTH([Date]), -- 数值排序字段

"MonthName", FORMAT([Date], "MMMM"),

"Quarter", "Q" & QUARTER([Date])

)

切片器及筛选值：

然后汇集对应度量值：

基础度量值：

总产量 =
CALCULATE(
SUM('生产成本明细表'[产量]),
ALLSELECTED('DateTable')
)

总生产成本 =
CALCULATE(
SUM('生产成本明细表'[总生产成本]),
ALLSELECTED('DateTable')
)

单位平均生产成本 =
DIVIDE(
[总生产成本],
[总产量],
BLANK()
)

环比度量值：

上期总产量 =
CALCULATE(
[总产量],
DATEADD('DateTable'[Date], -1, MONTH)
)

上期总生产成本 =
CALCULATE(
[总生产成本],
DATEADD('DateTable'[Date], -1, MONTH)
)

上期单位平均生产成本 =
CALCULATE(
[单位平均生产成本],
DATEADD('DateTable'[Date], -1, MONTH)
)

产量环比% =
DIVIDE(
[总产量] - [上期总产量],
[上期总产量],
BLANK()
)

成本环比% =
DIVIDE(
[总生产成本] - [上期总生产成本],
[上期总生产成本],
BLANK()
)

单位平均成本环比% =
DIVIDE(
[单位平均生产成本] - [上期单位平均生产成本],
[上期单位平均生产成本],
BLANK()
)

度量显示：

总产量显示 =
VAR CurrentValue = [总产量]
VAR MomChange = [产量环比%]
RETURN
IF(
ISBLANK(CurrentValue),
"─",
FORMAT(CurrentValue, "#,##0") & UNICHAR(10) &
IF(
ISBLANK(MomChange),
"● 无上月数据",
IF(MomChange > 0,
"🟢 +" & FORMAT(ABS(MomChange), "0.0%"),
IF(MomChange < 0,
"🔴 " & FORMAT(MomChange, "0.0%"), -- 负数会自动显示-号
"⚪ " & FORMAT(ABS(MomChange), "0.0%")
)
)
)
)

总成本显示 =
VAR CurrentValue = [总生产成本]
VAR MomChange = [成本环比%]
RETURN
IF(
ISBLANK(CurrentValue),
"─",
FORMAT(CurrentValue, "¥#,##0") & UNICHAR(10) &
IF(
ISBLANK(MomChange),
"● 无上月数据",
IF(MomChange > 0,
"🔴 +" & FORMAT(ABS(MomChange), "0.0%"), -- 成本增加用红色
IF(MomChange < 0,
"🟢 " & FORMAT(MomChange, "0.0%"), -- 成本减少用绿色
"⚪ " & FORMAT(ABS(MomChange), "0.0%")
)
)
)
)

单位成本显示 =
VAR CurrentValue = [单位平均生产成本]
VAR MomChange = [单位平均成本环比%]
RETURN
IF(
ISBLANK(CurrentValue),
"─",
FORMAT(CurrentValue, "¥#,##0.00") & UNICHAR(10) &
IF(
ISBLANK(MomChange),
"● 无上月数据",
IF(MomChange > 0,
"🔴 +" & FORMAT(ABS(MomChange), "0.0%"), -- 单位成本增加用红色
IF(MomChange < 0,
"🟢 " & FORMAT(MomChange, "0.0%"), -- 单位成本减少用绿色
"⚪ " & FORMAT(ABS(MomChange), "0.0%")
)
)
)
)

②存货柱状图

汇集度量值：

-- 存货当期产量
存货当期产量 =
CALCULATE(
SUM('生产成本明细表'[产量]),
ALLSELECTED('DateTable')
)

-- 存货当期总成本
存货当期总成本 =
CALCULATE(
SUM('生产成本明细表'[总生产成本]),
ALLSELECTED('DateTable')
)

-- 存货单位生产成本
存货单位成本 =
DIVIDE(
[存货当期总成本],
[存货当期产量],
BLANK()
)

环比度量值：

-- 存货上期产量
存货上期产量 =
CALCULATE(
[存货当期产量],
DATEADD('DateTable'[Date], -1, MONTH)
)

-- 存货上期单位成本
存货上期单位成本 =
CALCULATE(
[存货单位成本],
DATEADD('DateTable'[Date], -1, MONTH)
)

-- 存货产量环比%
存货产量环比% =
DIVIDE(
[存货当期产量] - [存货上期产量],
[存货上期产量],
BLANK()
)

-- 存货单位成本环比%
存货单位成本环比% =
DIVIDE(
[存货单位成本] - [存货上期单位成本],
[存货上期单位成本],
BLANK()
)

显示用：

-- 产量显示（含环比）
存货产量显示 = 
VAR CurrentValue = [存货当期产量]
VAR MomChange = [存货产量环比%]
RETURN
IF(
    ISBLANK(CurrentValue),
    "",
    FORMAT(CurrentValue, "#,##0") & " " &
    IF(
        ISBLANK(MomChange),
        "●",
        IF(MomChange > 0, 
            "▲+" & FORMAT(ABS(MomChange), "0.0%"),
            IF(MomChange < 0, 
                "▼" & FORMAT(MomChange, "0.0%"),
                "●" & FORMAT(ABS(MomChange), "0.0%")
            )
        )
    )
)

-- 单位成本显示（含环比）
存货单位成本显示 = 
VAR CurrentValue = [存货单位成本]
VAR MomChange = [存货单位成本环比%]
RETURN
IF(
    ISBLANK(CurrentValue),
    "",
    FORMAT(CurrentValue, "¥#,##0.00") & " " &
    IF(
        ISBLANK(MomChange),
        "●",
        IF(MomChange > 0, 
            "▲+" & FORMAT(ABS(MomChange), "0.0%"),
            IF(MomChange < 0, 
                "▼" & FORMAT(MomChange, "0.0%"),
                "●" & FORMAT(ABS(MomChange), "0.0%")
            )
        )
    )
)

图：

③产品品类产量柱状图

基本度量值：
品类总产量 = 
CALCULATE(
    SUM('生产成本明细表'[产量]),
    ALLSELECTED('DateTable')
)

-- 产品品类排名（用于排序）
品类产量排名 = 
RANKX(
    ALLSELECTED('产品主数据表'[产品品类]),
    [品类总产量],
    ,
    DESC,
    Dense
)

④成本结构环状图

度量值：

一级占比 =

DIVIDE(

SUM('成本结构明细表'[成本金额]),

CALCULATE(

SUM('成本结构明细表'[成本金额]),

ALLSELECTED('成本结构明细表'[成本类型])

)

二级占比 =

DIVIDE(

SUM('成本结构明细表'[成本金额]),

CALCULATE(

SUM('成本结构明细表'[成本金额]),

ALLSELECTED('成本结构明细表'[成本子类型])

)

图：

⑤产量成本趋势图

全时段日期建表：

全时段日期表 = 
VAR MinDate = MIN('生产成本明细表'[生产日期])
VAR MaxDate = MAX('生产成本明细表'[生产日期])
RETURN
ADDCOLUMNS (
    CALENDAR (MinDate, MaxDate),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "YearMonthDisplay", FORMAT([Date], "YYYY年M月"),
    "YearMonthNum", YEAR([Date]) * 100 + MONTH([Date])
)

趋势分析度量值：

-- 月度总产量（按全时段日期表的月份分组）
月度总产量 = 
CALCULATE(
    SUM('生产成本明细表'[产量]),
    ALLEXCEPT('生产成本明细表', '全时段日期表')
)

-- 月度总成本（按全时段日期表的月份分组）
月度总成本 = 
CALCULATE(
    SUM('生产成本明细表'[总生产成本]),
    ALLEXCEPT('生产成本明细表', '全时段日期表')
)

-- 月度单位成本
月度单位成本 = 
DIVIDE(
    [月度总成本],
    [月度总产量],
    BLANK()
)

图：