Forum Discussion
Summing measures from different tables
Hi try The following steps:
1- Create Measure for Hourly RateHourly Rate = CALCULATE( MAX(Rates[Hourly Rate]), FILTER( Rates, Rates[Facility] = SELECTEDVALUE('Hours Worked'[Facility]) && Rates[Provider Type] = RELATED(Headcount[Provider Type]) && Rates[Contract Type] = RELATED(Headcount[Contract Type]) ) )2- Create Measure for Scheduled Weekly Hours
Scheduled Weekly Hours = CALCULATE( SUM(Headcount[Scheduled Weekly Hours]), FILTER( Headcount, Headcount[NPI] = RELATED('Hours Worked'[Provider NPI]) ) )3- Calculate Total Pay
Total Pay = SUMX( 'Hours Worked', 'Hours Worked'[Total Hours] * [Hourly Rate] ) + SUMX( Headcount, [Scheduled Weekly Hours] * [Hourly Rate] )Dont forget to double check the table and column names.
Thank you.
Bibiano_Geraldo Thank you so much, but the Headcount table and the Rates table don't directly relate. The main table is the Hours Worked, which is time clock punches. It links to the rates table to find out the rates at that facility for that contract and provider type. The Hours Worked then links to the Headcount table, which is an employee table, to find out more info on the Provider on the punch. So i want to find the Provider on the PUnch, find out his scheduled hours (which is on Headcount), find out the rates for that facility, and find what his total pay is.
Hi try The following steps:
1- Create Measure for Hourly Rate
Hourly Rate =
CALCULATE(
MAX(Rates[Hourly Rate]),
FILTER(
Rates,
Rates[Facility] = SELECTEDVALUE('Hours Worked'[Facility]) &&
Rates[Provider Type] = RELATED(Headcount[Provider Type]) &&
Rates[Contract Type] = RELATED(Headcount[Contract Type])
)
)
2- Create Measure for Scheduled Weekly Hours
Scheduled Weekly Hours =
CALCULATE(
SUM(Headcount[Scheduled Weekly Hours]),
FILTER(
Headcount,
Headcount[NPI] = RELATED('Hours Worked'[Provider NPI])
)
)
3- Calculate Total Pay
Total Pay =
SUMX(
'Hours Worked',
'Hours Worked'[Total Hours] * [Hourly Rate]
) +
SUMX(
Headcount,
[Scheduled Weekly Hours] * [Hourly Rate]
)
Dont forget to double check the table and column names.
Thank you.