In theory could this work? Mapping master rep list to different reports/power pivot?

[pete234]

I've been working on something for a couple days involving sales reps to different reports. I'm trying to replace a report that has a bunch of annoying GETPIVOT references that were not designed with changing data in mind.

I've imported 5 reports into power pivot from another workbook
I've imported a master rep list into power pivot from Access.
I've imported a fiscal time table into power pivot from Access.
I've made a relationship between rep name on each report, to the master table by name.
No dupes there so ok. I've made a relationship to the date fields to the fiscal table.
I can make a pivot from one report to pull regional data contained on the rep report and pivot the report. But what I haven't figured out is how can I combine all the reports into one pivot.
It is mainly COUNT but there is also some SUM of revenue. In other words, I want to combine somewhat different data into one report, close to what the old manual report looked like, with the advantage of slicing by rep function. I guess if I have make 5 pivots it seems that I'm not leveraging all this mapping. So can anyone tell me how this can be done or if I'm on the wrong path? It seemed it was going to be nice and elegant, now I'm not sure.

 

[ruve1k]

Why do you have 5 reports? What is in each of the 5?

 

[pete234]

Why do you have 5 reports? What is in each of the 5?

I spent more time on this and it actually works for most of the numbers. One of the important columns came out to the cent to last week's manual version. These are reports from salesforce. Still working on getting some of the quirks ironed out but it should work well for loading into Sharepoint as soon as we upgrade, I don't think slicers will work without Powerpivot enabled. The only issue is in the logic of the relationships, as it won't do an Access #2 join as far as I know, only one-to-one so excludes no activity people from the pivot. Still learning but so far not bad.