Append Duplicate Data To Second Level Employees?

[discojulio]

Hello All,


I am a long time Excel user that is just now teaching myself PowerPivot. I've gotten the basics down and have imported data and created relationships. I've so far been unable to figure out how I might be able to do the following:


I am working on a project to allocate costs to overhead personnel. I have a list of bonuses that have been awarded to front line sales managers based on account names. Example:


Employee Account Sales Center Period Bonus
George Costanza Penske NYC Q415 $1000
George Costanza Vandalay NYC Q415 $750
Fred Flintstone Slate Bedrock Q415 $150
Jimmy Olsen Daily Planet Metropolis Q415 $300




Every quarter, when payments are made, I want to duplicate the rows of data based on the Sales Director level; assignments are in a separate table, like this:


Employee Sales Center Period Role
Johnny Sack NYC Q415 VP
Barney Rubble Bedrock Q415 VP
**** Gordon Metropolis Q415 SCH




Data can either be appended to the current table or added to a different executive-level payments table. Ideally the end result should look like the following:


Employee Account Sales Center Period Bonus Role
George Costanza Penske NYC Q415 $1000 SM
George Costanza Vandalay NYC Q415 $750 SM
Fred Flintstone Slate Bedrock Q415 $150 SM
Jimmy Olsen Daily Planet Metropolis Q415 $300 SM
Johnny Sack Penske NYC Q415 * VP
Johnny Sack Vandalay NYC Q415 * VP
Barney Rubble Slate Bedrock Q415 * VP
**** Gordon Daily Planet Metropolis Q415 * SCH

* Separate formula that allocates each executive's bonus proportionally based on revenue generated by each account in their Sales Center.


Any help pointing me in the right direction is greatly appreciated!

 

[Matt Allington]

I am a long time Excel user that is just now teaching myself PowerPivot. I've gotten the basics down and have imported data and created relationships.

If you are a competent Excel user then the good news is you can learn Power Pivot. The bad news is that there is quite a lot to learn before you truly will have the basics down.

Power Pivot is a reporting DB, not a relational DB. So there is no way to add new records inside PP - you need to do it elsewhere, such as Excel or a relational database. Once you have that sorted, you will need to build a star schema data model with lookup tables to support your data tables.

you would probably go faster if you invest some time to learn from a book written for Excel users (my book, but it is good - I promise) Learn to Write DAX - the book for all Excel users

if you want help, post a sample workbook here or on my forum PowerPivot Forum • Index page and I will help point you in the right direction.