Power Pivot on merged data

[fabads]

Dear all,


I am new on this forum and got some issues trying to merge data from different excel sources.


I've got two different excel files I would like to analyse using Powerpivot (or any other solution would be appreciated). Let me take an example:


File DB1.xlsx:

[B]Activity Project Cost[/B]
Act1 Proj1 10
Act2 Proj2 20
Act3 Proj2 30
Act4 Proj1 50

File DB2.xlsx:

[B]Activity Project Avail budget[/B]
Act1' Proj1 100
Act2' Proj2 200
Act3' Proj2 300
Act4' Proj1 600

I would like to compare my costs (file 1) against my allocated budget (file 2) by project. I cannot consider that activities names are not exactly the same between the 2 files.


Something like that:

[B]Project budget cost[/B]
Proj1 700 60
Proj2 500 50

I am using Powerpivot (Excel 2010) to build my my pivot table but unfortunately, there is a wrong calculation for the budget (1200!).


Maybe my problem is that I cannot identify Project as a joining key, because the names are not uniques (different activities linked to the same project).




Thanks for your help

 

[Kazlik]

You should be able to make your relationship on Project then create your measure ProjectBudget:=SUM(Sheet2[Avail budget]) measure in DB2 and then in your table pull in the Project and Cost from DB1 and the measure from DB2

 

[fabads]

You should be able to make your relationship on Project then create your measure ProjectBudget:=SUM(Sheet2[Avail budget]) measure in DB2 and then in your table pull in the Project and Cost from DB1 and the measure from DB2

Hello,

Thanks for your answer but I cannot make the relation between the 2 dabases using Project. Project contains duplicated values and PowerPivot needs unique values.

Fabrice

 

[fabads]

On the following picture, I simply add DB2 data: project as line labels and sum of available budget as measures

And on this picture, I add the Costs of DB1, but the sum is incorrect since the sum is done on all projects (110) and not project by project.

Any idea ?

 

[fabads]

I answer to myself: it seems that adding a third table, containing only project names and let's say a description solve the issue. It's a pity because I'd like to avoid managing another table. But it seems to work.