Scrum Team % Dax calculation

[PowerPaul]

Hi all, I have a data model in Excel thats using star schema, however there is 2 fact tables. 

The fact table is all of the charges for a project (Fact Project Details)

the second fact table is the scrum teams (Surajits Scrum)

(The project details contains no scrum teams info at all, only have employee id)

Dim Fiscal Calendar Table connected to Project details

Dim Employee table (Surajits Employee Data) connected to both fact tables.

Some Employees are on more than one scrum team and are assigned a % for each team. Example .49 and .51 on two different scrum teams. 

What I would like to do since the Fact Project Details contains more than employees charges but all charges like software and health insurance, etc. Each employee charge has an employee id associated with it. Each non employee charge has an employee id of 0. 

First I need a total of each employees charges for the month.

Then I need that total multiplied by the scrum team % to see the total charged to the scrum team. 

I keep getting an error that the tables are not linked. 

I am using excel and power pivot.

I would like my pivot to show the amount charged by scrum team by employee by month.

Is there a way to accomplish this pivot table using Dax measures?

Thank you.

[daXtreme]

Here's a solution. Please download the file from here (or the attachment) and see clearly that it works. I've included visuals for easy debugging and reconciliation. It's a pbix file but it's so easy that you'll have no issues implementing the solution into Excel. Enjoy!

[PowerPaul]

Hi daxtreme, thanks for having a go at this. 

I am getting the relationships may be needed error with your formula.

Total Cost =

    SUMX(

        Teams,

        CALCULATE( SUM( 'Fact'[Cost] ) ) * Teams[Team %]

    )

Remember I am using Excel Data Model, not power bi.

My pivot will have scrum team name in rows followed by employee name.

Columns will have Date hierarchy so I can show by year and expand to month.

Your measure Total cost will be in the values section of the pivot.

At first glance, it appears to be correct, however I am getting the following error so I cannot trust the results. 

In addition I have one months of data so I can only show one month and cannot verify it will work over more months. 

Please advise:

Thank you.