Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ilooper
Frequent Visitor

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

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.

 

Data Model.jpg

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @ilooper ,

 

Is there a relationship between Cost and Patients tables?

My Sample:

Patients:

RicoZhou_0-1649140372047.png

Cost:

RicoZhou_2-1649140383757.png

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.

RicoZhou_3-1649140514224.png

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @ilooper ,

 

Is there a relationship between Cost and Patients tables?

My Sample:

Patients:

RicoZhou_0-1649140372047.png

Cost:

RicoZhou_2-1649140383757.png

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.

RicoZhou_3-1649140514224.png

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
Super User
Super User

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?

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.