使用ISINSCOPE自定义小计和总计

 

函数用法：

ISINSCOPE(COLUMN)

函数返回TRUE和FALSE

 

自定义总计：

MeasureB = SUMX(Sheet1, [QTY] * [UNIT])

ISINSCOPE A =

SWITCH(ISINSCOPE(Sheet1[PRODUCT]),

    TRUE(), [MeasureB],

    FALSE(), "NOT IN SCOPE",

    2

)

 

自定义小计和总计： 

ISINSCOPE B =

SWITCH(TRUE(),

    ISINSCOPE(Sheet1[PRODUCT]),[MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[PRODUCT])),

    ISINSCOPE(Sheet1[CATEGORY]), [MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[CATEGORY])),

    [MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[PRODUCT]))

) //SWITCH里面需要按照层级由低到高取逐层判断

ISINSCOPE C =

SWITCH(TRUE(),

    ISINSCOPE(Sheet1[CATEGORY]), [MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[CATEGORY])),

    ISINSCOPE(Sheet1[PRODUCT]),[MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[PRODUCT])),

    [MeasureB]/CALCULATE([MeasureB], ALLSELECTED(Sheet1[PRODUCT]))

)

 

 这里可以看出B和C的区别，C因为SWITCH判断里面CATEGORY在前面，所以PRODUCT值都是1

 

自定义多层总计行：

首先在Power Query里面给数据源添加一个索引列【Index】，然后用DAX新建一列Column = 1

新建度量值：

ISINSCOPE CATEGORY = IF(ISINSCOPE(Sheet1[Index]), VALUES(Sheet1[CATEGORY]), BLANK())

ISINSCOPE1PPRODUCT =

IF(ISINSCOPE(Sheet1[Column]),

    IF(ISINSCOPE(Sheet1[Index]),VALUES(Sheet1[PRODUCT]), "SUB TOTAL"), "GRAND TOTAL"

)

拖拽图表：

 关闭自动换行，将Column和Index宽度变成最窄

 

总结：

ISINSCOPE多层级嵌套判断的时候需要从最细的颗粒度开始，可以根据实际需求灵活定义表格Total展示的内容