Power BI 套路：在按类别划分的趋势线图中加入综合参考线

dayslee 2018-04-30

展开全文

场景

用Power BI显示不同产品类别按时间的销售利润率如下：

度量值

订单累计利润率 % = DIVIDE( SUM( 'tb订单'[利润] ) , SUM( 'tb订单'[销售额] ))

可视化

设置如下：

效果：

问题

问题来了，虽然知道不同类别按照时间的订单累计利润率，但从业务上来看，有没有参考呢？每月的整体利润率呢？可以加入到该图中吗？

很遗憾，Power BI并不直接支持这种想法，那我们需要变相来实现，利用技巧，曲线救国。

想要的效果：

这里有一条汇总线作为整体参考，就像是股票的大盘，如果个股好于大盘，则个股表现良好，否则就是脱了后腿。

实现

既然默认情况下无法满足需要，那就来自行构建需要的效果。

问题抽象

为了在图例中加入汇总，也就是在类别的同一级别增加一个类目叫“汇总”。并实现如下逻辑：

如果是普通分类，就按原度量值（利润率）计算；
如果是选择汇总，就计算度量值（利润率）的全局表现。

注意，这里并没有提及业务，并把利润率的业务语义放入括号，这种抽象描述便于总结出套路。

第一步：构造新的分类

新的分类应该满足：

包括原有分类；
增加一个总计项；
新的分类应该能用来进行筛选（切片）。

新建一个计算表：

类别带汇总 = VAR base = VALUES( 'tb产品'[类别] )VAR base_add_total = UNION( base , ROW( '类别' , '汇总' ) )RETURN base_add_total

结果：

为了能够进行筛选和切片，与原有分类建立联系：

第二步：构造新的度量值

为了实现逻辑：

如果是普通分类，就按原度量值（利润率）计算；
如果是选择汇总，就计算度量值（利润率）的全局表现。

建立新的度量值：

订单累计利润率（含汇总） % = IF(     SELECTEDVALUE( '类别带汇总'[类别] ) = '汇总' ,     CALCULATE( DIVIDE( SUM( 'tb订单'[利润] ) , SUM( 'tb订单'[销售额] ) ) , ALL( 'tb产品' ) ) ,    [订单累计利润率 %])

在该度量值中，如果是“汇总”，那就计算全局性的度量值，这里必须注意忽略产品分类表的影响，所以使用ALL来处理。

第三步：构建可视化

有了我们需要的X轴：日期，Y轴：度量值，图例：类别（含汇总）就可以构建出：

优化

优化是对问题解决方案的进一步完善，很多问题是没有完美解决方案的，例如本例中的模型：

这个关系的建立显得特别生硬，而且除了在这里使用这个技巧外，这个辅助表也别无他用，在Power BI / DAX数据模型中应该减少主体模型本身的表，在这里可以不使用关系。

让这个辅助表在模型外部，显得模型更加紧凑。由于没有了关系，可视化无法完成正常的筛选效果，如下：

所有的类别图例线合成在一起，原因请大家自己思考。（提示：新类别失去关系，无法筛选）

可以在度量值中，来构建虚拟关系（高级技巧），修改度量值如下：

订单累计利润率（带汇总） % = VAR f = TREATAS( VALUES( '类别带汇总'[类别] ) , 'tb产品'[类别] )RETURN IF(     SELECTEDVALUE( '类别带汇总'[类别] ) = '汇总' , CALCULATE( DIVIDE( SUM( 'tb订单'[利润] ) , SUM( 'tb订单'[销售额] ) ) , ALL( 'tb产品' ) ) ,    CALCULATE( [订单累计利润率 %] , f ))

效果如下：

说明：