Excel(Power BI)热线

Gladwin. · 发表于 2014-10-31 02:51:07

thanks

平平淡淡. · 发表于 2014-10-31 04:44:23

楼主好人！
正好有个问题，我们现在用的是Excel 2010，大部分机器只能支持32位的power pivot add-in（虽然我的可以支持64位的）。现在我做的Pivot table很简单，就是发票按供应商金额汇总排序（因为发现32位power pivot add-in 居然不能做Distinct count ），在Slicer 里面，能不能做Dynamic 的Top X？比如Top 5， Top 10。。。我知道自己可以在Table里选，但是希望能做在Slicer里面这样其他不懂Excel的人只要在Slicer里面按一下就好了。不知道32位的power pivot add-in能不能做？还是只有64位或者Excel 2013才可以？
因为Copyright的问题，现在公司里只能用Excel 2010。
谢谢！

平平淡淡. · 发表于 2014-10-31 05:04:57

"

有时间我会尽量回复

decei. · 发表于 2014-10-31 05:10:20

flat26a 发表于 2014-9-19 10:01
楼主好人！
正好有个问题，我们现在用的是Excel 2010，大部分机器只能支持32位的power pivot add-in（虽然 ...
可以做的，没问题。distinct count 和32 bit还是64bit 没关系，两者都有。建议发个sample workbook给我，toy data 就可以。联系方式看首帖

平平淡淡. · 发表于 2014-10-31 07:32:49

平平淡淡发表于 2014-9-19 11:09

可以做的，没问题。distinct count 和32 bit还是64bit 没关系，两者都有。建议发个sample workbook给我， ...
谢谢楼主！我发给你Gmail邮箱了，请查收

cocobai. · 发表于 2014-10-31 09:04:10

flat26a 发表于 2014-9-19 14:45

谢谢楼主！我发给你Gmail邮箱了，请查收
I have sent the completed file back to your email. You can  also download the sample workbook from below link.
https://onedrive.live.com/?cid=7 ... 5E31C189063EC%21124
I know you probably do not know Power Pivot much. The key to address your issue is having a disconnected table ( do not set up relationship with your main table), in your case it's the TOP X table with one column. You can google Disconnected Table and find lots of interesting post on this top for Power Pivot.
I start off by building the base measure Spend = SUM(DATA[AMOUNT])
                                                            Number of Supplier = DISTINCTCOUNT(DATA[VENDOR_NAME])
then I rank your category based on Spend  CategoryRank = RANKX(ALL(DATA[Category]),DATA[Spend])
Finally the driver for Dynamic TOP N count is hidden measure(hide it as your users don't need to see it)
IF(HASONEVALUE('TOP'[TOP X]),IF(DATA[CategoryRank]
2014-9-19 23:43:11 上传
下载附件 (78.27 KB)

2014-9-19 23:43:12 上传
下载附件 (25.52 KB)

All the formula are exclusive to Power Pivot which is DAX formula and they are not part of normal Excel functions.
There you have it. Dynamic TOP N (what ever you like to call it)