How to calculate a sum per project from a fact table and a dim table

[Anonymous]

table1 contains projectskey,  labourcost

Table 2 contains projectskey, ProjectNumber

These two tables are related by projectskey

How to calculate the sum per project.

ex:

table1

projectskey  labourcost

1000                10

1001                 15

1002                20

Table2

projectskey  ProjectNuber

1000     A

1001     A

1002     B

I want to add a new column to table2

projectskey  ProjectNuber Sum

1000     A        25

1001     A        25

1002     B        20

Does someone know how to do that in DAX

Regards

[ChrisMendoza · Accepted solution]

@Anonymous -

how about :

Column = 
CALCULATE(
    SUM(table1[labourcost]),
    ALLEXCEPT(table2,table2[ProjectNuber])
)

[Anonymous]

Try the following in Table2 as a calculated column:

Labour sum =
SUMX ( RELATEDTABLE ( Table1 ), Table1[LabourCost] )

RELATEDTABLE brings across a filtered version of Table1 (the filter is the ProjectKey from the Table2 row), then SUMX sums the labour costs.

[Anonymous]

Hello,

I have tried you solution and we are pretty close.  It gives me the total amount per project and per projectskey.

I want the total amount per project indepedently of the projectskey.

Do you have an idea how to do not consider the projectskey.

[Anonymous]

Okay, try the following:

Labour Costs Per Project =
CALCULATE (
    SUM ( Table1[LabourCost] ),
    FILTER ( Table2, Table2[Project] = EARLIER ( Table2[Project] ) )
)

Here EARLIER is doing the heavy lifting in filtering Table2 for the SUM by checking each Project value to the one in the current row.

[Anonymous]

No, it does not like the earlier statement