ABC分类按切片器切片

shufeabb

thanks, see attached.

shwangfeng

简单回复下，水平有限，套用须谨慎：

动态ABC是一种常见的需求，因此熟练以后可以作为套路使用，不过要小心应用场景——例如这里筛选列和计算列不是同一列。

ABC的算法你已经掌握，只要把相同的逻辑转换成度量值(注意充分理解计算流程中筛选条件的变化)即可：

先写一个简单的求和度量值Sales做铺垫(只是为了给其他度量值引用)：

1. =SUMX('分类','分类'[销售金额])

复制代码

然后是核心度量值：

1. =
   
2. VAR PartNumbers =
   
3.     CALCULATETABLE (
   
4.         DISTINCT ( '分类'[Part number] ),
   
5.         ALL ( '分类' ),
   
6.         ALLSELECTED ( '分类'[PC] )
   
7.     ) //注意最后一项不要写成DISTINCT()或者VALUES()，因为它们会被当前行/列筛选，导致各个单元格的总集不一样从而算错
   
8. // 这里偷懒直接用ALL('分类'[Part number])也可以，因为下面计算Sales时仍然会考虑PC列筛选，然后无销售额的会被过滤
   
9. VAR AllSales =
   
10.     SUMX ( PartNumbers, [Sales] ) // 所有已选择项在当前筛选条件下的Sales的和，即总额(分母)
    
11. VAR CurrentSales = [Sales] // 固定当前项的销售额，下面引用的时候值不会变，因此不再需要EARLIER()
    
12. VAR LargeSales =
    
13.     SUMX ( FILTER ( PartNumbers, [Sales] >= CurrentSales ), [Sales] ) //ABC核心(分子)
    
14. VAR SalesPercent =
    
15.     DIVIDE ( LargeSales, AllSales )
    
16. VAR Result =
    
17.     SWITCH ( TRUE (), SalesPercent < 0.8, "A", SalesPercent < 0.9, "B", "C" )
    
18. RETURN
    
19.     Result

复制代码

然后将PartNumber拖到[行]字段，将此度量值拖到[值]字段，根据PC制作切片器，这样就可以了。

累计百分比其中是倒数第二步，所以只要RETURN这一步就可以了(用FORMAT稍微格式化一下)：

1. =VAR PartNumbers =
   
2.     CALCULATETABLE (
   
3.         DISTINCT ( '分类'[Part number] ),
   
4.         ALL ( '分类' ),
   
5.         ALLSELECTED ( '分类'[PC] )
   
6.     )
   
7. VAR AllSales =
   
8.     SUMX ( PartNumbers, [Sales] )
   
9. VAR CurrentSales = [Sales]
   
10. VAR LargeSales =
    
11.     SUMX ( FILTER ( PartNumbers, [Sales] >= CurrentSales ), [Sales] )
    
12. VAR SalesPercent =
    
13.     DIVIDE ( LargeSales, AllSales )
    
14. RETURN
    
15.     FORMAT(SalesPercent,"0.0%")

复制代码

同样拖到[值]字段就可以了。

不过还有一个小问题，如果你像附件中那样将其他列拖到[行]字段，而且这些额外字段没有全部折叠，计算就会发生错误，这是因为透视表中会将[行]字段中的多列合并成一个tuple做筛选，在计算的时候触发arbitrarily shaped filters流程，所以上面的写法要适当修改：

1. =
   
2. VAR PCs =
   
3.     ALLSELECTED ( '分类'[PC] )
   
4.     //先固定外部切片器所选项，注意不能用ALLSELECTED()替换下面PCs
   
5. VAR PartNumbers =
   
6.     CALCULATETABLE ( DISTINCT ( '分类'[Part number] ), ALL ( '分类' ), PCs )
   
7.     //必须清除全表筛选，然后恢复外部PC列筛选，这里有点绕，要细细想，下同
   
8. VAR AllSales =
   
9.     CALCULATE ( SUMX ( PartNumbers, [Sales] ), ALL ( '分类' ), PCs )
   
10. VAR CurrentSales = [Sales]
    
11. VAR LargeSales =
    
12.     CALCULATE (
    
13.         SUMX ( FILTER ( PartNumbers, [Sales] >= CurrentSales ), [Sales] ),
    
14.         ALL ( '分类' ),
    
15.         PCs
    
16.     )
    
17. VAR SalesPercent =
    
18.     DIVIDE ( LargeSales, AllSales )
    
19. VAR Result =
    
20.     SWITCH ( TRUE (), SalesPercent < 0.8, "A", SalesPercent < 0.9, "B", "C" )
    
21. RETURN
    
22.     Result

复制代码

累计百分比同样改就好了。

越简单越不容易错，尽量使用前面的写法，保护[行]字段没有累赘的列。

参考.zip (873.28 KB, 下载次数: 8)

2020-3-10 16:51 上传

点击文件名下载附件

百度 DAX PowerBI 帕累托 ABC 之类的关键字也可以看到别的解法，只是要注意别人的场景和需求是否与你的一致。

shwangfeng

关于ALLSELECTED()我研究了一下，发现上面部分写法不是很好，主要原因是ALLSELECTED()和ALL()同时用作CALCULATE的筛选参数时，ALLSELECTED的效果会被ALL()覆盖，放到本例中就是无效化，等于只写了一个ALL()——虽然本例中不影响结果。
我始终没有找到官方对于“两个ALL*()同时作为Modifier出现，孰先孰后，互相如何影响”的直接说明，最后只能先暂时下这个结论，请务必注意。


不过解决办法也很简单，舍弃CALCULATE(......,ALLSELECTED())的写法，改为像上面一样用VAR XX = ALLSELECTED(XX)这样先取得外部筛选器固定下来，后面直接引用变量就没有问题了，从一个Modifier变成了普通的表筛选，不再会和ALL()冲突，应当是很安全的办法。

这个问题主要是由于本例中"筛选列和计算列不是同一列"引起的，因为要清除当前行筛选，改为由外部切片器决定行总集，这样来保持所有行共用一个总集，因此不得不用ALLSELECTED()，而恰好ALLSELECTED()又非常复杂，又恰好要和ALL()同时作用……

如果有大佬通达这一块，请务必指点迷津。