Using PowerPivot to Merge Two Tables

[eliwaite]

I have two tables, that I need to merge. One table includes the sales information per account, the second table includes the sales staff assigned to each account. Each table includes the account ID, which I am using to connect the two tables. Here is a brief example of the two tables:

Account ID	Name Of Account	Sales
1	Apple	$500
2	Google	$0
3	Microsoft	$200

<tbody>
</tbody>

Account ID	Sales Person Name
1	Jim
1	Jane
2	John
3	James
3	Janet
3	Joe

<tbody>
</tbody>

What I would like the output to look like is:

Apple

Jim​

Jane​

Google

John​

Microsoft

James​

Janet​

Joe​

This way, I can sort by sales (or the other various metrics I have on the account level) and immediately know which sales people to praise or provide "additional guidance" to.

The output I can currently getting is:

Row Labels

Apple

James

Jane

Janet

Jim

Joe

John

Google

James​

Jane​

Janet​

Jim​

Joe​

John​

Microsoft

James

Jane

Janet

Jim

Joe

John

Grand Total

<tbody>
</tbody>

Any help in getting me to my desired output would be greatly appreciated.

Thank you,
Eli

 

[scottsen]

I feel like... you gave a level of partial information which is confusing me. I guess I would expect to see some "Per Sales Person" data that I do not see?

Since you only have sales info at the Account level, there is much you can say about a Sales Person...

 

[eliwaite]

There are no metrics associated with the sales people, only with the account. Basically I want to take my list of account and say, show me all the account that have over a million dollars in sales last quarter and the sales people associated with these accounts. Right now I am stuck making a list of accounts that fit my criteria and manually going through them one by one and adding the correct sales staff, which is very tedious.

 

[scottsen]

This is one of the weirder "things" in Power Pivot for me. And I mis-read your question a bit.

This is the old "Miami, TX" doesn't exist... and sometime excel seems to "try" to calculate that, and sometimes it is magically filtered out by the dax engine.

The most technical description here:
MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering

The italians here:
AutoExist and Normalization – SQLBI

Some of my own comments here:
http://tinylizard.com/power-pivot-hierarchical-data/

Summary... if both columns are from THE SAME TABLE, you will get what you want. So, if in your sales table you add a calc column for =RELATED(Accounts[AccountName]), then a pivot table w/ Sales Person Name and that new column... will filter as expected.

 

[eliwaite]

Thank you, that worked flawlessly.