PowerPivot计算过滤器不起作用 [英] PowerPivot Calculate filters wont work
问题描述
我有以下表格
订单：
OrderID ||费用|季度|用户
-------------------------
1 | 10 | 1 | 1
2 | 15 | 1 | 2
3 | 3 | 2 | 1
4 | 5 | 3 | 3
5 | 8 | 4 | 2
6 | 9 | 2 | 3
7 | 6 | 3 | 3
目标：
UserID |名称|目标|季度|总和
-----------------------------
1 |约翰| 20 | 1 | x
1 |约翰| 15 | 2 | x
2 |鲍勃| 12 | 2 | x
2 |鲍勃| 15 | 3 | x
3 |荷马| 5 | 3 | x
3 |荷马| 7 | 4 | x
现在我要做的是在第二个表中汇总所有具有UserID的订单
我的第一次尝试是：
= CALCULATE（sum（Orders [Cost]）; Orders [Quarter] = [Quarter]; Orders [User] = [UserID]）
结果应为：
UserID |名称|目标|季度|总和
- --------------------------
1 |约翰| 20 | 1 | 10
1 |约翰| 15 | 2 | 3
2 |鲍勃| 12 | 2 | 15
2 | Bob | 15 | 3 | 8
3 |荷马| 5 | 3 | 11
3 |荷马| 7 | 4 | 0
但是在这里我得到了错误：用户ID列不能为在该表达式中找到或未使用。
要尝试一些操作，我使用了固定值：
= CALCULATE（sum（Orders [Cost]）; Orders [Quarter] = 2; Orders [User] = 1）
这不会导致错误消息，但也不能完全正常工作。我现在得到
UserID | ... | Sum
1 | ... | 3
1 | ... | 3
2 | ... |
2 | ... |
3 | ... |
3 | ... |
仅供参考：SUM中的空白区域实际上是空白的。
疯狂的事情是我是否执行以下操作：
= CALCULATE（sum（Orders [Cost]）; Orders [Quarter] = 2; Orders [User] = Orders [User]）
我得到：
UserID | ... | Sum
1 | ... | 3
1 | ... | 3
2 | ... |
2 | ... |
3 | ... | 11
3 | ... | 11
这几乎是我想要的。但是我不明白，为什么有些单元格可以正常工作。
有什么方法可以使我的工作正常吗？
Urganot ，
如果您能充分利用所有内容，则所需的输出非常简单PowerPivot中可用的功能-主要是能够创建（只需将UserID从表Orders和Goals拖到用户表）。看起来应该像这样：
一旦完成，就很容易获得所需的数字。只需定义两个新的计算字段（Powerpivot功能区->计算字段-> 新计算字段）。确保选择用户的目标表并输入以下公式：
订单金额总和
= SUM（Orders [Cost]）
对于总计目标或目标目标
= SUM（目标[目标]）
在Excel 2013中，界面看起来像这样。
要按季度查看数字，您需要还有一个具有唯一四分之一数字的表格-只需按照创建 Users 表格的步骤操作即可。同样，在关系图视图中链接到其他表，您应该以此结束：
将Quarter表用作。
I have the following tables
Orders:
OrderID|Cost|Quarter|User
-------------------------
1 | 10 | 1 | 1
2 | 15 | 1 | 2
3 | 3 | 2 | 1
4 | 5 | 3 | 3
5 | 8 | 4 | 2
6 | 9 | 2 | 3
7 | 6 | 3 | 3
Goals:
UserID|Name |Goal|Quarter|Sum
-----------------------------
1 |John | 20 | 1 | x
1 |John | 15 | 2 | x
2 |Bob | 12 | 2 | x
2 |Bob | 15 | 3 | x
3 |Homer| 5 | 3 | x
3 |Homer| 7 | 4 | x
Now what I want to do is in the second table sum up all orders that have the UserID from the current row and are in the current rows quarter.
My first attempt was:
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=[Quarter];Orders[User]=[UserID])
The result should be:
UserID|Name |Goal|Quarter|Sum
-----------------------------
1 |John | 20 | 1 | 10
1 |John | 15 | 2 | 3
2 |Bob | 12 | 2 | 15
2 |Bob | 15 | 3 | 8
3 |Homer| 5 | 3 | 11
3 |Homer| 7 | 4 | 0
But here I get the error: Column "User ID" cannot be found or may not be used in this expression.
To try something out, I used fixed values:
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=1)
Which resulted not in an error message but not fully working either. I now get
UserID|...|Sum
1 |...| 3
1 |...| 3
2 |...|
2 |...|
3 |...|
3 |...|
FYI: the empty space in SUM is really empty.
The crazy thing is if I do the following:
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=Orders[User])
I get:
UserID|...|Sum
1 |...| 3
1 |...| 3
2 |...|
2 |...|
3 |...| 11
3 |...| 11
Which is almost what I want. But I dont understand, why some cells are working.
Is there any way to make this work as I want to?
Urganot,
The desired output is quite simple if you take advantage of all features available in PowerPivot - mainly the ability to create relationships between datatables.
I have copied you data and there is was one thing missing - a table of Users. You can get one from your database/IT department, or simple create it in Excel from the data you already have available. Just make sure it doesn't contain any duplicates, otherwise you won't be able to define the relationships.
Datatables I am using in my next steps look like this (User table is on the right):
Add all the table to Powerpivot data-model (PowerPivot ribbon -> Add to Data Model), switch to diagram view and connect Orders and Goals tables to User table (simply drag UserID from tables Orders and Goals onto Users table). It should look like this:
Once this is done, it's very easy to get the numbers you want. Just define two new Calculated Fields (Powerpivot ribbon -> Calculated Fields -> New Calculated Field). Make sure you pick the target table for Users and enter this formula:
For Sum of Order Value
=SUM(Orders[Cost])
For Sum or Target Goals
=SUM(Goals[Goal])
In Excel 2013, the interface looks like this.
In order to see numbers by quarter, you would need to have a table with unique quarter numbers as well - simply follow the steps for creating Users table. Again, link in Diagram view to other tables and you should end up with this:
Using Quarter table as a slicer makes it probably the best way how to analyze/filter the numbers:
So that's the answer: relationships in PowerPivot.
It could be very difficult to do it otherwise, not to mention slow once you start dealing with thousands of rows. I believe this approach is much better compared to "the old way" (nested LOOKUPs & IFs).
Also, keep in mind that the data you use is not structured in the best way - especially quarters are not distinguished by year etc.
Anyway, I hope this helps - if there is anything unclear, just let me know in the comment section. Or simply go ahead and download my source Excel file (2013 version).
这篇关于PowerPivot计算过滤器不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
