PowerPivot Calculate Filters Wont Work

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

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