求解如何用Power Pivot求指定年纪和科目的前10名

xiaoyidemm

今天在函数板块看到一个案例，如图B3和D3分别设置了下拉选项，想要获得选的的年纪+科目得分的前10名显示在下面，如果成绩相同则并列都显示。有一位大神回复是用的PQ解决的问题，好像是用分组管理+自定义函数解决的。求助这基本逻辑思路是怎样的，PQ这个没接触过，函数还可以看公式分析。这个我看不懂，麻烦大家了




动态筛选查找.zip (153.36 KB, 下载次数: 25)

2018-11-2 15:53 上传

点击文件名下载附件

xiaoyidemm

朱荣兴 发表于 2018-11-2 16:14
何必想的那么神秘，这个东西，VBA代码也可以轻松搞定的呀

这。。VBA也不懂呀。。就会些函数。有一个大神用INDEX+OFFSECT解决的，但函数公式特别长，而且好像没有判断死一定是取前10名。所以想。。

PowerBI极客

这个题最简单的方式是用Power Pivot，PQ解比较繁琐、交互也不方便。想了解详细思路可以去问给你提供PQ解法的人

xiaoyidemm

PowerBI极客 发表于 2018-11-2 17:04
这个题最简单的方式是用Power Pivot，PQ解比较繁琐、交互也不方便。想了解详细思路可以去问给你提供PQ解法 ...

说错了，不好意思。就是Excel里面的Power Pivot的模块做的。说是用分组管理还是分层管理的方式。开始以为PQ和Power Pivot是一个东西

PowerBI极客

rank:=RANKX(ALLSELECTED('table'[姓名]),CALCULATE(SUM('table'[分数])),,DESC,Dense)
top10 score:=IF([rank]<=10,CALCULATE(SUM('table'[分数])))
top10 score:=IF([rank]<=10,CALCULATE(SUM('table'[分数])))
top10 rank:=IF([rank]<=10,[rank])

下面是效果图和数据源

PowerBI极客

xiaoyidemm 发表于 2018-11-2 17:12
说错了，不好意思。就是Excel里面的Power Pivot的模块做的。说是用分组管理还是分层管理的方式。开始以为 ...

你发的文件是用pq做的呀

xiaoyidemm

PowerBI极客 发表于 2018-11-2 18:03
rank:=RANKX(ALLSELECTED('table'[姓名]),CALCULATE(SUM('table'[分数])),,DESC,Dense)
top10 score:= ...

感谢您，这个隐藏模块一直没接触过。

xiaoyidemm

PowerBI极客 发表于 2018-11-2 18:06
你发的文件是用pq做的呀

是的呢。。。弄错了。搞不太清楚那两个东西。刚报了PQ和Power Pivot 的学习班

kongkong2011

用了几种常见的数据透视表，PowerQuery，Power Pivot，DAX Query 的作法，供参考：

1，Power Query & 数据透视表（Pivot Table）最简单


2，纯Power Query和数据验证，比原附件简单一些，没有自定义函数，基本都是界面操作，数据刷新慢是因为导入数据模型的关系。



3，Power Query + Dax Query，刷新慢

1. EVALUATE
   
2. SUMMARIZE (
   
3.     TOPN (
   
4.         10,
   
5.         CALCULATETABLE (
   
6.             'Test-PP',
   
7.             'Test-PP'[年级] IN VALUES ( Grade ),
   
8.             'Test-PP'[科目] IN VALUES ( Subject )
   
9.         ),
   
10.         'Test-PP'[成绩], DESC
    
11.     ),
    
12.     'Test-PP'[班级],
    
13.     'Test-PP'[姓名],
    
14.     'Test-PP'[成绩]
    
15. )
    
16. ORDER BY 'Test-PP'[成绩] DESC

复制代码

4，Power Query + Power Pivot

1. =RANKX(ALLSELECTED('Test-PP'),[Score],,DESC)

复制代码

附件：

动态筛选查找.7z (229.61 KB, 下载次数: 54)

2018-11-17 00:39 上传

点击文件名下载附件