Reference value of another field in set anlysis expression while using TOTAL

[kwdaniels]

Our consulting company has multiple offices and multiple projects. Each resource (employee) is assigned to an office. Each project is based in an office. Usually the resources work on projects based in their own office. Sometimes, however, a resource from one office is "exported" to work on a project based in another office.

Here is some very simplified sample data for just two offices (Dallas and New York) showing that all revenue generated by Dallas resources was for projects based in Dallas, while revenue generated by New York resources was split between projects based in New York ($300) and Dallas ($200). In other words, New York exported some of their resources to work on Dallas projects.

Raw Data

We would like to create a report listed by resource office showing the total revenue generated by resources belonging to that office ("Resource Office Revenue") and revenue generated by projects based in that same office ("Project Office Revenue") as follows:

Desired Output

See also attached qvf file showing my attempt to generate the desired output. My attempt to display "Project Office Revenue" works only if you first filter on the Resource Office, which unfortunately allows you to see only one Resource Office at a time in the table. We would like to be able to view all offices at the same time.

Here's my attempted formula for Project Office Revenue:

sum(TOTAL {<[Resource Office] = , [Project Office] = P([Resource Office])>} [Revenue])

Is there a way to revise this expression to allow all rows to be displayed together without first filtering on a Resource Office?

[kwdaniels]

Does anyone have any insight into this problem? Thanks in advance!