Summing measures from different tables

[aashton]

In Power BI, I have an Hours Worked table with the fields Provider NPI, Facility, Contract Type, Provider Type, Total Hours.  This links many to one to a Headcount table, on NPI, where I get this providers weekly scheduled hours.  The HOurs Worked table also links to a Rates table many to one, on Facility/Provider Type/Contract Type combo to find the hourly rate.  Taking Total Hours Worked from the Hours Worked table and multiplying by the hourly rate works fine.  I also need to take this providers Scheduled Weekly Hours from the Headcount table, times the hourly rate, and see what an expected weekly pay is.  This is where there are problems.  It seems the Headcount table and the Rates table dont' "talk" to each other.  How can I set this up to make it work?

[Bibiano_Geraldo]

Your need to relationship the Headcount table and the Rates table, find what can be the id to relate this tables. 

For example:

1. Create Calculated Column in the Headcount Table:

HeadcountKey = Headcount[Facility] & "-" & Headcount[Provider Type] & "-" & Headcount[Contract Type]

2. Create Calculated Column in the Rates Table:

RatesKey = Rates[Facility] & "-" & Rates[Provider Type] & "-" & Rates[Contract Type]

3. Create Relationship:

Once the columns are created, you can manually set the relationship between Headcount[HeadcountKey] and Rates[RatesKey] in the Model view of Power BI. This ensures the Headcount table can access the rates based on the combination of the three fields.

[aashton]

@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.

[Bibiano_Geraldo · Accepted solution]

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.