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

Question

Hello,&nbsp;This is probably an easy one.&nbsp; I have site level information on the revenue side.&nbsp; I do not on the cost side.&nbsp; What I'm trying to do is write a measure that helps me allocate cost to each site (facility) by utilization.&nbsp; In this case it would be how many patients we performed in that given month.&nbsp;How I do it in my excel model that works really well is create a ratio of patients done in a month for that market by site. example&nbsp;&nbsp;Houston market performed a total of 100 patients in month of Feb 22site 1 did 50 patients = .5site 2 did 25 patients = .25site 3 did 25 patients = .25site 4 did 0 patients = 0.0&nbsp;Then I mutiply the total cost for the market by the ratio.&nbsp; If total cost is 10,000site 1 = 5,000site 2 = 2,500site 3 = 2,500site 4 = 0&nbsp;I'm not sure how to create this ratio or how I would mutiply it by the cost for the market.&nbsp; So I can see what is my cost by site.&nbsp; Later I will want to figure out my margin by subtracting the site level revenue by the cost by revenue.&nbsp; Any points in the right direction would be helpfull.&nbsp;

anonymous · Accepted Answer

Hi&nbsp;ilooper&nbsp;,
&nbsp;
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
    _RATECost =
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]
&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

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

2 Replies

Related content

Page Level filter for different groups

Creating a Date Filter in Power BI

Creating summary Table with different filters

Create Custom Group By Filter for Different Scenarios

Different schema into different destination