cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Need help calculating weighted average

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

Re: Need help calculating weighted average

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver II
Resolver II

Re: Need help calculating weighted average

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

Highlighted
Super User IV
Super User IV

Re: Need help calculating weighted average

@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 !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Need help calculating weighted average

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors