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:
<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
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