Multiple table relationships

[denisl]

I found powerpivot a couple of days ago and am just getting started using it.

I have 3 csv files that I load into powerpivot.
Here's an example -

main.csv:

Name,Value
A,100
B,200
A,300
B,400
B,500
C,600
C,700
A,800

file1.csv

Name,Value_A
A,1
B,2
C,3

file2.csv

Name,Value_B
A,1000
B,2000
C,3000

I load all three files into power pivot under "get external data - text"
I create the following two relationships:
1. main.csv "Name" to lookup table file1.csv "Name"
2. main.csv "Name" to lookup table file2.csv "Name"

Then I generate the Pivot table and have the 3 data sources to build my pivot table.
I select from main.csv - Name and Value
I select from file1.csv - Value_A
I select from file2.csv - Value_B

The resulting table looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Value_A[/TD]
[TD]Sum of Value_B[/TD]
[TD]Sum of Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]6000[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]6000[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]6000[/TD]
[TD]1300[/TD]
[/TR]
</tbody>[/TABLE]


So my question is why does the sum of Value_B add up to the total of all the Value_B numbers in file2.csv? For A it should be 1000, B it should be 2000 and C it should be 3000 - yet it's showing 6000 (the sum of all the Value_B's in file2.csv). The Sum of Value_A and Value is working fine.

This is an example - my actual data is 3 very large CSV files with over 2M rows and about 30 columns.

Thank you

 

[Jacob Barnett]

Its absolutely not cheating and if the performance of your model is acceptable then it's a good solution.

Creating relationships on calculated columns can cause performance issues with large models but if you don't foresee having to work with massively increased data volumes then I would be inclined to stick with your solution!