114_Power Pivot 销售订单之销售额、成本、利润率相关

2020年3月1日22:31:49 5 617 1940字阅读6分28秒

焦棚子的文章目录

请在文末下载附件

 

一、背景

双十二回来后遇到一个比较有意思的计算销售额和利润率的需求(见下文说明)。

先看下效果。

114_Power Pivot 销售订单之销售额、成本、利润率相关
结果
114_Power Pivot 销售订单之销售额、成本、利润率相关

说明:

1、订单表中不体现销售价格,如上图《出库》。

2、销售价格单独管理,如上图《价格表》;

说明:价格是不断调整的,以上图红色填充的sku1为例,

2018/12/1至2018/12/10销售价:592,

2018/12/11至2018/12/13销售价:581,

2018/12/14后销售价:500。

3、库存每月1号给出一个当月期初库存如上图《期初库存》,包含成本价和数量

4、入库商品的成本价是浮动的如上图中黄色填充sku1,

2018/12/1期初成本价:201,

2018/12/10入库成本价:201,

2018/12/11入库成本价:211,

约定:成本价按照加权平均求得

以sku1为例:

总成本=201*100+201*100+211*200=82400

总数量=100+100+200=400

成本单价=82400/400=206

二、数据源

1、出库

114_Power Pivot 销售订单之销售额、成本、利润率相关

2、期初库存

114_Power Pivot 销售订单之销售额、成本、利润率相关

3、入库

114_Power Pivot 销售订单之销售额、成本、利润率相关

4、价格表

114_Power Pivot 销售订单之销售额、成本、利润率相关

5、关系

114_Power Pivot 销售订单之销售额、成本、利润率相关

三、上度量值

1、订单数量

订单数量:=DISTINCTCOUNT('出库'[订单id])

2、出库

出库:=SUM('出库'[数量])

3、期初

期初:=SUM('期初库存'[数量])

4、入库

入库:=SUM('入库'[数量])

5、销售价

销售价:=SUM('价格表'[售价])

6、库存

库存:=
VAR TF1 =
    IF ( MAX ( '出库'[日期] ) < MAX ( '日期表'[date] ), FALSE (), TRUE () )
VAR TF2 =
    ISFILTERED ( '日期表'[date] )
RETURN
    SWITCH (
        TRUE (),
        TF1 && TF2, CALCULATE ( '度量值'[期初], DATESMTD ( '日期表'[date] ) )
            + CALCULATE ( '度量值'[入库], DATESMTD ( '日期表'[date] ) )
            - CALCULATE ( '度量值'[出库], DATESMTD ( '日期表'[date] ) ),
        TF2 = FALSE (), CALCULATE ( '度量值'[期初], DATESMTD ( '日期表'[date] ) )
            + CALCULATE ( '度量值'[入库], DATESMTD ( '日期表'[date] ) )
            - CALCULATE ( '度量值'[出库], DATESMTD ( '日期表'[date] ) )
    )

7、销售额

销售额:=
SUMX (
    ADDCOLUMNS (
        '出库',
        "单价", CALCULATE (
            [销售价],
            FILTER (
                ALL ( '价格表' ),
                '价格表'[日期] <= '出库'[日期]
                    && '价格表'[日期]
                        >= TOPN (
                            1,
                            CALCULATETABLE (
                                VALUES ( '价格表'[日期] ),
                                FILTER ( ALL ( '价格表' ), '价格表'[日期] <= '出库'[日期] && '价格表'[sku] = '出库'[sku] )
                            ),
                            '价格表'[日期], DESC
                        )
                    && '价格表'[sku] = '出库'[sku]
            )
        )
    ),
    '出库'[数量] * [单价]
)

8、成本

成本:=
VAR DS =
    STARTOFMONTH ( '出库'[日期] )
VAR DE =
    ENDOFMONTH ( '出库'[日期] )
VAR T1 =
    DATESBETWEEN ( '日期表'[date], DS, DE )
VAR T2 =
    SUMMARIZE (
        sku,
        sku[sku],
        "CC", DIVIDE (
            SUMX (
                CALCULATETABLE ( '期初库存', DS, ALL ( '日期表'[date] ) ),
                '期初库存'[成本价] * '期初库存'[数量]
            )
                + SUMX ( CALCULATETABLE ( '入库', T1, ALL ( '日期表'[date] ) ), '入库'[成本价] * '入库'[数量] ),
            SUMX ( CALCULATETABLE ( '期初库存', DS, ALL ( '日期表'[date] ) ), '期初库存'[数量] )
                + SUMX ( CALCULATETABLE ( '入库', T1, ALL ( '日期表'[date] ) ), '入库'[数量] )
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS ( '出库', "成本单价", SUMX ( FILTER ( T2, [sku] = '出库'[sku] ), [CC] ) ),
        [成本单价] * [数量]
    )

9、利润率

利润率:=DIVIDE('度量值'[销售额]-'度量值'[成本],'度量值'[销售额])

四、总结

1、根据业务创建相关度量值上下文;

2、本案例中难点主要在度量值6、销售额和7、成本;

3、销售额中价格巧妙使用TOPN,成本中对加权求平均dax的转换。

4、如有先进先出的需求,体现在批次管理即可,本案例不是先进先出的案例。

 

by焦棚子

焦棚子的文章目录

请点击【立即购买】或者【升级VIP】获得案例附件。

隐藏内容需要支付:¥10
立即购买 升级VIP
焦棚子
  • 本文由 发表于 2020年3月1日22:31:49
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接
140_Power BI&Power Pivot之降维展示同类型比较 Power BI

140_Power BI&Power Pivot之降维展示同类型比较

焦棚子的文章目录 请在文末下载附件 一、背景 最近一段时间比较忙,几乎没有时间更新网站内容,今天刚好周末,更新一个简单的需求。 上效果图: 在我们日常做对比分析的时候,经常几个相同维度(比如省份、区域...
138_Power BI&Power Pivot特殊半累加度量 Power BI

138_Power BI&Power Pivot特殊半累加度量

焦棚子的文章目录 请在文末下载附件 一、背景 半累加度量(semi-additive measure),在DAX建模分析的时候经常遇见;应用场景诸如银行存款、库存等。 接下来看一个比较有意思的案例(结...