PowerPivot Calculate filters wont work

Question

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?

Answer 1

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).