cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilky
Helper III
Helper III

Need help calculating weighted average

Hi,

I have a reference table which holds state wise average length of stays (ALOS) in hospital for different procedures.  I also have a fact table which holds admission episodes and LOS for each patient . I would like to calculate the weighted average LOS using the state ALOS. 

ilky_1-1596897357960.png

 

Example of data is shown in the picture.  The formula for Weighted State  ALOS is :

=(H8/H10)*$H$3+(H9/H10)*$H$4 for Jan 

=(I8/I10)*$H$3+(I9/I10)*$H$4 for Feb and so on ..

I am unable to create the DAX measure which calculates the Weighted State ALOS.

Can you please help ?

 

 

 

 

1 ACCEPTED SOLUTION
Arklur
Resolver II
Resolver II

I manually put your sample data in a model, here is the PQ code for that:

 

// StateAverage
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s5LTVXSUTJVitWJVvLILACyDQ2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Procedure = _t, StateAverageLos = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Procedure", type text}, {"StateAverageLos", Int64.Type}})
in
    #"Changed Type"

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JR8s5LTQVSZkqxOtFKbqlJCCFjsJBvYhFCyAIs5FiAJGQJVVWJEDICC3mVIhlvCBEC2+iRWQA3HWIhRMQMyT6IiDmSdRARUyTbICJGxki2QYRMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Procedure = _t, Procedures = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Procedure", type text}, {"Procedures", Int64.Type}})
in
    #"Changed Type"

 

 

After that, create the necessary relationship between the 2 tables:

image.png

 

Then add this measure to the model:

 

WeightedStateAverageLos = 
VAR vWeightedSum = 
    SUMX ( 
        Data,
        Data[Procedures] * RELATED ( StateAverage[StateAverageLos] )
    )
VAR vCountProcedures = 
    SUM ( Data[Procedures] )
VAR vRetval = 
    DIVIDE ( vWeightedSum, vCountProcedures )
RETURN
    vRetval

 

 

This should give you the expected result:

image.png

 

Obviously, to see the months in the correct order, there are more transformation needed, but that's not the scope of your question.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@ilky , this not the way data should be there.

Can you share sample data and sample output in table format?

 

Refer

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Arklur
Resolver II
Resolver II

I manually put your sample data in a model, here is the PQ code for that:

 

// StateAverage
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s5LTVXSUTJVitWJVvLILACyDQ2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Procedure = _t, StateAverageLos = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Procedure", type text}, {"StateAverageLos", Int64.Type}})
in
    #"Changed Type"

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JR8s5LTQVSZkqxOtFKbqlJCCFjsJBvYhFCyAIs5FiAJGQJVVWJEDICC3mVIhlvCBEC2+iRWQA3HWIhRMQMyT6IiDmSdRARUyTbICJGxki2QYRMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Procedure = _t, Procedures = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Procedure", type text}, {"Procedures", Int64.Type}})
in
    #"Changed Type"

 

 

After that, create the necessary relationship between the 2 tables:

image.png

 

Then add this measure to the model:

 

WeightedStateAverageLos = 
VAR vWeightedSum = 
    SUMX ( 
        Data,
        Data[Procedures] * RELATED ( StateAverage[StateAverageLos] )
    )
VAR vCountProcedures = 
    SUM ( Data[Procedures] )
VAR vRetval = 
    DIVIDE ( vWeightedSum, vCountProcedures )
RETURN
    vRetval

 

 

This should give you the expected result:

image.png

 

Obviously, to see the months in the correct order, there are more transformation needed, but that's not the scope of your question.

View solution in original post

Hi @Arklur 

Thank you so much, this is exactly what I needed. I am still new in Power BI , your explanation was clear and simple to understand.

Thanks again.

Regards

Ilky

Greg_Deckler
Super User
Super User

@ilky - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!