105_Power Pivot财务科目(层级深度&筛选深度)

2020年3月1日12:28:31 4 999 2159字阅读7分11秒

焦棚子的文章目录

请在文末下载附件

1、背景

在财务科目中,需要按照科目层级来显示;在excel中都是用公式来实现,而且对于数据的管理及更新是一件头痛的事情,那么PP来做一个如何呢。

2、必备知识
前提知道PATH相关父子层级的公式及ISFILTERED不知道的去看看官网解释。

传送门:

Parent and Child Functions (DAX)

ISFILTERED 函数 (DAX)

3、上案例

105_Power Pivot财务科目(层级深度&筛选深度)
维度表科目ID与事实表科目ID建立1:n的关系

在pp模型中处理下维度表的层级。

105_Power Pivot财务科目(层级深度&筛选深度)

新建列公式如下:

PATH=PATH('科目管理'[科目ID],'科目管理'[父级ID])
一级=LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],1,1))
二级=IF(LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],2,1))=BLANK()
,'科目管理'[一级]
,LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],2,1)))
三级=IF(LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],3,1))=BLANK()
,'科目管理'[二级]
,LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],3,1)))
四级=IF(LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],4,1))=BLANK()
,'科目管理'[三级]
,LOOKUPVALUE('科目管理'[科目名称],[科目ID],PATHITEM('科目管理'[PATH],4,1)))
是否末级=
var s='科目管理'[科目ID]
return
(CALCULATE(COUNTROWS('科目管理'),ALL('科目管理'),'科目管理'[父级ID]=s)=0)*1
PATHLENGTH=PATHLENGTH('科目管理'[PATH])

正常情况下,在事实表,建立total然后拉透视表即可。

105_Power Pivot财务科目(层级深度&筛选深度)
事实表建立total
科目total:=SUM('科目明细'[value])

模型中建立层级

105_Power Pivot财务科目(层级深度&筛选深度)

常态下的透视表

105_Power Pivot财务科目(层级深度&筛选深度)

于是,我们发现其中蓝色的部门显示的比较重复了,这样的报表不是很友好。(其中绿色是一种特殊情况,下文讲解)

所以这样写个普通的度量值是不行的,所以我们需要对度量值进行优化。

在这里我们需要引入两个概念:层级深度,筛选深度

筛选深度

F0:=ISFILTERED('科目管理'[一级])+ISFILTERED('科目管理'[二级])+ISFILTERED('科目管理'[三级])+ISFILTERED('科目管理'[四级])

层级深度

PL:=MAX('科目管理'[PATHLENGTH])

于是我们得到这样一个透视表

105_Power Pivot财务科目(层级深度&筛选深度)

通过观察红色区域我们可以发现,这些需要显示的的都是层级深度>=筛选深度,即PL>=F0

优化后公示如下:

VV1:=VAR F0 = ISFILTERED ( '科目管理'[一级] ) 
       + ISFILTERED ( '科目管理'[二级] )
       + ISFILTERED ( '科目管理'[三级] )
       + ISFILTERED ( '科目管理'[四级] )
VAR PL =MAX ( '科目管理'[PATHLENGTH] )
VAR V0 = '科目明细'[科目total]
RETURN
    SWITCH(
    TRUE (), 
    PL >= F0, V0
    	  )
105_Power Pivot财务科目(层级深度&筛选深度)

大多数财务科目这样显示就完成了,因为遵循的都是上一级科目=下一级科目总和

拓展

但是绿色的情况,还没有说明,因为有的情况,不遵循上一级科目=下一级科目总和,更多的是出现在业务管理中,如人事层级,上级和下级做同样的事情,需要展现包含自己和下属的明细及总和。

于是末级科目大于0,非末级科目就等于0

MJ:=SUM ( '科目管理'[是否末级] ) = 0
105_Power Pivot财务科目(层级深度&筛选深度)
关注total和MJ

VV2中,第二个条件MJ * V0 > 0 && F0 = PL + 1,适合这种非末级科目,具体情况需要具体分析,这里给出的是一种思路。

VV2:=VAR F0 = ISFILTERED ( '科目管理'[一级] ) 
       + ISFILTERED ( '科目管理'[二级] )
       + ISFILTERED ( '科目管理'[三级] )
       + ISFILTERED ( '科目管理'[四级] )
VAR PL =MAX ( '科目管理'[PATHLENGTH] )
VAR MJ =SUM ( '科目管理'[是否末级] ) = 0
VAR V0 = '科目明细'[科目total]
RETURN
    SWITCH(
    TRUE (), 
    PL >= F0, V0,
    MJ * V0 > 0 && F0 = PL + 1, V0
    	  )
105_Power Pivot财务科目(层级深度&筛选深度)
结果2重点关注绿色区域

4、总结

总结下父子层级主要理解path相关公式,同时对ISFILTERED理解,其中对层级深度及筛选深度需要深入理解,才能对透视表更灵活的展示。

参照圣经Handling parent-child hierarchies章节

是否末级字段使用的var变量写法,如果是早期版本用earlier

传送门:EARLIER 函数​docs.microsoft.com

 

by焦棚子

焦棚子的文章目录

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

隐藏内容需要支付:¥10
立即购买 升级VIP
焦棚子
  • 本文由 发表于 2020年3月1日12:28:31
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接
142_Power BI之同比预测 Power BI

142_Power BI之同比预测

焦棚子的文章目录 请在文末下载附件 一、背景 最近刚好在做一个简单同比预测的模型,预测方法很简单,就是累计同比预测,把MTD展示出来。 二、数据源 1、日期表:Calendar 2、事实表1:Data...
140_Power BI&Power Pivot之降维展示同类型比较 Power BI

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

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