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

[ilooper]

Hello,

This is probably an easy one.  I have site level information on the revenue side.  I do not on the cost side.  What I'm trying to do is write a measure that helps me allocate cost to each site (facility) by utilization.  In this case it would be how many patients we performed in that given month.

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 

Houston market performed a total of 100 patients in month of Feb 22

site 1 did 50 patients = .5

site 2 did 25 patients = .25

site 3 did 25 patients = .25

site 4 did 0 patients = 0.0

Then I mutiply the total cost for the market by the ratio.  If total cost is 10,000

site 1 = 5,000

site 2 = 2,500

site 3 = 2,500

site 4 = 0

I'm not sure how to create this ratio or how I would mutiply it by the cost for the market.  So I can see what is my cost by site.  Later I will want to figure out my margin by subtracting the site level revenue by the cost by revenue.  Any points in the right direction would be helpfull.

[Anonymous · Accepted solution]

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.

[lbendlin]

You can do this the hard way (via crossjoin or M:Mrelationship), or you could cheat and merge these tables in PowerQuery to "augment" the header level table with the detail level weightings. What's your preference?