Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 

 

 

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Anonymous -

 

how about :

 

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





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Anonymous -

 

how about :

 

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





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

No, it does not like the earlier statement

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.