Power Pivot relationship - same values for each row

[tommyts]

Fairly new to PowerPivot and data modeling here, but learning quickly, and could use some help from the pros... that's you guys! Any help will be greatly appreciated!!

A very straightforward situation: I'm trying to build a pivot table that compares financial "actuals" to "budget", by account number. I created a relationship between the "Account PandL" table ("Account number" column) and the "Budget 2015 PL" table (also "Account number" column), and get the results below..... The totals in the Budget 2015 PL column are all the same. (for reference, the top row notes from which table each column/field is derived)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]table:
[/TD]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl65, width: 162"]"Account- PandL"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD="class: xl65, width: 151"]Profit and Loss Summ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 166"]
<tbody>[TR]
[TD="class: xl65, width: 166"]Budget 2015 PL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]field:[/TD]
[TD]Account number[/TD]
[TD]1/31/15 Actuals[/TD]
[TD]1/31/15 Budget[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4010[/TD]
[TD]0[/TD]
[TD]1120621.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4020[/TD]
[TD]5021.25[/TD]
[TD]1120621.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4030[/TD]
[TD]117.19[/TD]
[TD]1120621.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4040[/TD]
[TD]23,421.25[/TD]
[TD]1120621.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4050[/TD]
[TD]117,851.64[/TD]
[TD]1120621.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4060[/TD]
[TD](2,502.11)[/TD]
[TD]1120621.66[/TD]
[/TR]
</tbody>[/TABLE]


The "Account PandL" table and the "Profit and Loss Summ" tables are from a separate professional third-party database storage software and have a relationship with each other through a field called "LinkTo(For)AccountID", which is a complex proprietary coding system (which I don't want to touch!).

What am I doing wrong??

Again... thank you!

 

[ImkeF]

Hi tommyts,
I'm not sure if you're using 2 or 3 tables here? From your text I understand that you ony have 2 (AccountsPandL and Budget 2015).
Even if you Budget table would only consist of one row for each account, it's not good practice to link 2 fact tables directly with each other. Therefore you should use an accounts table with one row for each account and link the facts to it.
If thats what you have done already, there might be an issue with your account No formats (so that the Budget ones don't match with your accounts table) - so make sure that they are really identically formatted.
hth, Imke

 

[tommyts]

Hi tommyts,
I'm not sure if you're using 2 or 3 tables here? From your text I understand that you ony have 2 (AccountsPandL and Budget 2015).
Even if you Budget table would only consist of one row for each account, it's not good practice to link 2 fact tables directly with each other. Therefore you should use an accounts table with one row for each account and link the facts to it.
If thats what you have done already, there might be an issue with your account No formats (so that the Budget ones don't match with your accounts table) - so make sure that they are really identically formatted.
hth, Imke

Thanks for the reply Imke. There are 3 tables. 'Accts-PandL' and 'ProfitLossSumm' already have a relationship, linked by account I.D. (this relationship was created by a third-party and works perfectly). I'm trying to establish a relationship between 'AcctsPandL' and 'Budget', based on account number. There are no duplicate values in either table (each account number has only 1 row), but there are many account numbers in 'AcctsPandL' which are not on the 'Budget' table.

The result I'm trying to get is to list each account number from the master 'AcctPandL' table, then show the value for each account from EACH of the other tables, 'Budget' and 'ProfitLossSumm', comparing budget to actuals.

Further help will be greatly appreciated! thank you!

 

[ImkeF]

Have you checked the formats of your key (account no)? How does that Pivot table look if you delete the Profit and Loss Sum Col? Still showing the different accounts or just one row (blank)?

 

[scottsen]

In general, when you see a bunch of grand total looking cells... the first thought in your head should be "for some reasons... filters are not being applied to those rows". Understanding that relationships have a "direction", and filters applied to the one side get applied to the many side... but NOT the other way around.

In your case, you THINK that AccountNum (that you have on rows) is filtering your Budget Table... but clearly, it is not. I pic of the relationships would help say whats up. You want the field on Rows to come from the lookup table (the 1 side of the 1:many).

 

[tommyts]

In general, when you see a bunch of grand total looking cells... the first thought in your head should be "for some reasons... filters are not being applied to those rows". Understanding that relationships have a "direction", and filters applied to the one side get applied to the many side... but NOT the other way around.

In your case, you THINK that AccountNum (that you have on rows) is filtering your Budget Table... but clearly, it is not. I pic of the relationships would help say whats up. You want the field on Rows to come from the lookup table (the 1 side of the 1:many).

Thanks for the explanation, scottsen. I'll fiddle around with it, keeping your explanation in mind, and get back with my results..... could be a week though, my wife is about to go into labor!! woohoo! Thanks guys!