请在文末下载附件
1、背景
在财务科目中，需要按照科目层级来显示；在excel中都是用公式来实现，而且对于数据的管理及更新是一件头痛的事情，那么PP来做一个如何呢。
2、必备知识
前提知道PATH相关父子层级的公式及ISFILTERED不知道的去看看官网解释。
传送门：
Parent and Child Functions (DAX)
3、上案例
在pp模型中处理下维度表的层级。
新建列公式如下：
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)*1PATHLENGTH=PATHLENGTH('科目管理'[PATH])正常情况下，在事实表，建立total然后拉透视表即可。
科目total:=SUM('科目明细'[value])模型中建立层级
常态下的透视表
于是，我们发现其中蓝色的部门显示的比较重复了，这样的报表不是很友好。（其中绿色是一种特殊情况，下文讲解）
所以这样写个普通的度量值是不行的，所以我们需要对度量值进行优化。
在这里我们需要引入两个概念：层级深度，筛选深度。
筛选深度
F0:=ISFILTERED('科目管理'[一级])+ISFILTERED('科目管理'[二级])+ISFILTERED('科目管理'[三级])+ISFILTERED('科目管理'[四级])层级深度
PL:=MAX('科目管理'[PATHLENGTH])于是我们得到这样一个透视表
通过观察红色区域我们可以发现，这些需要显示的的都是层级深度>=筛选深度，即PL>=F0
优化后公示如下：
VV1:=VAR F0 = ISFILTERED ( '科目管理'[一级] )
+ ISFILTERED ( '科目管理'[二级] )
+ ISFILTERED ( '科目管理'[三级] )
+ ISFILTERED ( '科目管理'[四级] )
VAR PL =MAX ( '科目管理'[PATHLENGTH] )
VAR V0 = '科目明细'[科目total]
RETURN
SWITCH(
TRUE (),
PL >= F0, V0
)
大多数财务科目这样显示就完成了，因为遵循的都是上一级科目=下一级科目总和
拓展
但是绿色的情况，还没有说明，因为有的情况，不遵循上一级科目=下一级科目总和，更多的是出现在业务管理中，如人事层级，上级和下级做同样的事情，需要展现包含自己和下属的明细及总和。
于是末级科目大于0，非末级科目就等于0
MJ:=SUM ( '科目管理'[是否末级] ) = 0
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
)
4、总结
总结下父子层级主要理解path相关公式，同时对ISFILTERED理解，其中对层级深度及筛选深度需要深入理解，才能对透视表更灵活的展示。
参照圣经Handling parent-child hierarchies章节
另是否末级字段使用的var变量写法，如果是早期版本用earlier
传送门：EARLIER 函数
by焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论