Creating a measure - divide filtered sum by count on different table

Hi ilooper ,

Is there a relationship between Cost and Patients tables?

My Sample:

Patients:

Cost:

In my sample there is no active relationship between two tables.

Rate =
VAR _Total =
    CALCULATE (
        SUM ( Patients[Amount] ),
        ALLEXCEPT ( Patients, Patients[Year/Month] )
    )
VAR _Amount =
    SUM ( Patients[Amount] )
VAR _RATE =
    DIVIDE ( _Amount, _Total )
RETURN
    _RATE

Cost =
VAR _Cost_CurrentMonth =
    CALCULATE (
        SUM ( Cost[Cost] ),
        FILTER ( Cost, Cost[Year/Month] = MAX ( Patients[Year/Month] ) )
    )
RETURN
    _Cost_CurrentMonth * [Rate]

Result is as below.

If there is a relationship between two tables, like [Year/Month] to [Year/Month]. It can make Cost code easier.

Cost =
VAR _Cost_CurrentMonth =
    CALCULATE (
        SUM ( Cost[Cost] )
    )
RETURN
    _Cost_CurrentMonth * [Rate]

Best Regards,
Rico Zhou

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