中国式报表学习解析-多级表头&自定义小计

继续之前的解析,Power BI制作的基本中国式报表依旧存在问题,一是列小计显示不合理,最后一列的总计是不存在的,二是表头层级只有两层,准确来说,除去维度名称,只有一层,如果说类别这里想要往下一层进行查看呢?比如下面这样的效果。

与之前比,多了一个层级,列小计完全根据需要自定义,放弃使用自带的列小计功能,由DAX度量计算。表头与度量值改进如下

改进后的多级表头:

NewTable-2 = 
VAR __t_Year = ADDCOLUMNS(SELECTCOLUMNS(VALUES('日期'[年份名称]),"L1_表头",[年份名称]),"L2_表头","","L0_维度","Year","Orderby",1)

VAR __t_Catalog = ADDCOLUMNS(DISTINCT(SELECTCOLUMNS('产品',"L1_表头",[类别],"L2_表头",[子类别])) ,"L0_维度","Catalog","Orderby",1)

Var __t_total =  ADDCOLUMNS(SELECTCOLUMNS(VALUES('产品'[类别]),"L1_表头",[类别]),"L2_表头","小计","L0_维度","Catalog","Orderby",2)

Return UNION(__t_Year,__t_Catalog,__t_total)

由于展示的时候小计需要放到后面,所以L2级表头需要按Orderby列进行排序

改进后的度量值计算公式:

NewMeasure2 =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'NewTable-2'[L0_维度] ) = "Year", CALCULATE (
        SUM ( '订单'[销售额] ),
        TREATAS ( VALUES ( 'NewTable-2'[L1_表头] ), '日期'[年份名称] )
    ),
    SELECTEDVALUE ( 'NewTable-2'[L0_维度] ) = "Catalog", IF (
        SELECTEDVALUE ( 'NewTable-2'[L2_表头] ) = "小计",
        CALCULATE (
            SUM ( '订单'[销售额] ),
            TREATAS (
                CALCULATETABLE (
                    VALUES ( 'NewTable-2'[L2_表头] ),
                    VALUES ( 'NewTable-2'[L1_表头] ),
                    ALL ( 'NewTable-2'[L2_表头], 'NewTable-2'[Orderby] )
                ),
                '产品'[子类别]
            )
        ),
        CALCULATE (
            SUM ( '订单'[销售额] ),
            TREATAS ( VALUES ( 'NewTable-2'[L2_表头] ), '产品'[子类别] )
        )
    )
)

看起来复杂了很多,其实只是多了一步,那就是小计的计算。这是值得研究的一部分,一是因为涉及到筛选条件的设计,容易出错,另一个是这里隐含一个容易出错的点。

这里小计计算的是 "在’产品'[类别]下计算’产品'[子类别]的合计",所以要从表头中筛选出正确的中间表与’产品'[子类别]创建虚拟关系。

新建的表头中除了维度,总共就三列,L2是需要被筛选的列,先去掉全部筛选,然后Orderby看上去没直接影响,但也必须放入ALL去除筛选,然后保留L1表头的筛选,因为这里小计是在L1表头下的小计。最后DAX代码如下,通过测试,确认该代码是符合预期的。

TREATAS(
    CALCULATETABLE(
        VALUES('NewTable-2'[L2_表头]),
        VALUES('NewTable-2'[L1_表头]),
        ALL('NewTable-2'[L2_表头],'NewTable-2'[Orderby])
    ),
    '产品'[子类别]
)

需要注意的、隐含的点是:Orderby列一定要放到ALL函数中去除筛选,因为L2表头使用Orderby作为排序依据,原因未知,如果不使用该排序,那么放不放都正常,但效果就不符合预期了。

引用参考: https://www.jianshu.com/p/c8770edeba77

发表评论

电子邮件地址不会被公开。 必填项已用*标注