Trying to generate a report with a multi-multi relationship

[Telperion]

I'm trying to follow the instructions here, but I can't seem to apply the tutorial to my scenario: A mystifying and awesome solution for many 2 many « PowerPivotPro

My tables are related to mail opening and payment processing. The fields relevant to this question are Date, Operation, Operation method, and User. I also track time spent opening mail per User per Operation per Date. I'm trying to join a table such that I perform calculations on the aggregated data for each Operation for each Date The relationships are as follows:

• tblOpening: Many Users can open many Operations on a given Date
• tblWork: One Operation can have many Methods

Example: User A opens Operation 1, 2, and 3 on 3/3/14. User B opens Operations 2, 3, and 4 on 3/4/14.
- I want to know how much of each Operation was opened on a given Date.

Example #2: Operation 2 can be run by hand, or on a high speed processor.
- I want to know how many of each Operation was run on a given Date regardless of Operation Method

I can aggregate the data I need separately by running a pivot table report on each table, that's not a problem. The problem is that the kind of reports and forecasts I want to run require me to be able to join the tables. The kinds of things I'm looking to run are reports or forecasts like:

- Payments processed per 1 envelope (Forecast being if we receive in 10 trays of mail, how many payments should we expect to run that day?)
- How many payments can be processed for a given Operation given 1 hour of a user's opening time.
- If we expect to process an additional 100,000 payment items next year how many extra hours per-day of mail opening will that take. (Future staff allocation, hiring, etc)

And so on, so forth, etc, etc. I included all the examples and sample reports so that it hopefully helps in crafting the right solution for joining the tables.

Any thoughts?

 

[Jacob Barnett]

In terms of answering your question about the 'Many to Many', what you need is a 'Operation' table that has a column of unique Operations which can be linked to the Operations columns on each of your existing reports. An SQL query or Power Query may be the quickest way to get to this.

It should then be straight forward enough to write measures based on the relevant table e.g. counting rows on tblOpening to determine number of openings.

HTH
Jacob