Need help calculating weighted average

[Anonymous]

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. 

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 ?

[amitchandak]

@Anonymous , 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

[Arklur · Accepted solution]

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:

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:

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

[Anonymous]

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]

@Anonymous - 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.