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:

Row Labels	Sum of Value_A	Sum of Value_B	Sum of Value
A	1	6000	1200
B	2	6000	1100
C	3	6000	1300

<tbody>
</tbody>

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!