Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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.
Solved! Go to 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.
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.
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?
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.