Calculations between two fact tables

[Anonymous]

Dear PBI-Community,

I have a data model that looks something like this:

My main Problem is calculating between the two fact tables. I have to multiply the hours worked per month with the corresponding hourly wage for each employee and i haven't figured out how to do that. The goal is to multiply the values of "hours worked" and "hourly wage" where the date and Employee_ID are similar between both fact tables.

Additional Info: I have to use direct query and can't change the structure of the data in the data warehouse. My current strategy is to generate a new primary key (YYYY.MM.Employee_ID) and directly connect the two fact tables but I think there has to be a "cleaner" way. 

Thank for the help,

Jakob

[Anonymous · Accepted solution]

@Anonymous 

I created some data:

Employee Fact Table:

Employee Wage Fact Table

Here are the steps you can follow：

1. Create calculated column.

Month = MONTH('Employee Fact Table'[Date])

2. Create measure.

Measure =

var _1=CALCULATE(SUM('Employee Fact Table'[Hour worked]),FILTER(ALL('Employee Fact Table'),'Employee Fact Table'[Employee_ID]=MAX('Employee Fact Table'[Employee_ID])&&'Employee Fact Table'[Month]=MAX('Employee Fact Table'[Month])))

var _2=CALCULATE(SUM('Employee Wage Fact Table'[Hourlywage]),FILTER(ALL('Employee Wage Fact Table'),'Employee Wage Fact Table'[Employee_ID]=MAX('Employee Fact Table'[Employee_ID])&&'Employee Wage Fact Table'[Month]=MAX('Employee Fact Table'[Month])))

return

_1 * _2

3. Result:

If it does not meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , A measure like this

Sumx(summarize('Employee Fact', 'Employee Dim'[Employee], 'Date'[Date] , "_1", sum('Employee Fact'[hours worked]) , "_2" , sum('Employee Wages'[hourly wage] )), [_1]*[_2])

Do calculation changes as per need