How to calculate weighted Average in correct way?

[bikelley]

Hello,

Can anybody please tell or correct me whether I doing my weighted average by status calculation right? I did some research but I am not sure and confident about my process. Please see below,

I am trying to get a weighted average by case status,

Formula,

Weight(Days) = CALCULATE(SUM('Project_dim'[Days]), ALLEXCEPT('Project_dim', 'Project_dim'[Days]))
WTD = SUM('Project_dim'[Days])/ [Weight(Days)]
WTD AVG = [WTD] * COUNT('Project_dim'[Project ID])

Please correct me if I am wrong or suggest to me the best way I can do this.

Thank you so much for your time. I really appreciate it.

[Anonymous]

Hi @bikelley 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[ryan_mayu]

@bikelley 

wha't s the expected result and what's the calculation logic?

[bikelley]

@ryan_mayu , 

Thank you for taking the time to look at my issue. I am not sure that is why I asking you all for help.  I need to get the weights average by status. Do you have suggestions? Again, thank you so much. 

[ryan_mayu]

@bikelley 

maybe try 

=averagex(values(status), [WTD avg])

[bikelley]

@ryan_mayu ,

Thank you for the quick response. Can you please tell me why we have to do averagex? I got the total days and divided them by days for each status. Then multiply that answer by project count. Do you think that is wrong? 

[ryan_mayu]

@bikelley 

based on the screenshot you shared, what's your expected result?

What's more , what's your calculation logic?

You just shared your DAX. If there is something wrong with your DAX, that means the DAX logic is also wrong. It's also hard for us to get the correct logic.

[bikelley]

@ryan_mayu 

Honestly, I am not sure. I am just trying to get the weighted average by status, and I came up with the above Dax formula. I just want to make sure that is correct. Thank you so much for the help. 

[Anonymous · Accepted solution]

Hi @bikelley 

I build a sample  to explain code to you.

1. This code will calculate total days for each Days.

Weight(Days) = CALCULATE(SUM('Project_dim'[Days]), ALLEXCEPT('Project_dim', 'Project_dim'[Days]))

You see all days =5 will get result =10.

Make sure whether Status is Days, you said you want to get the total days for each status. If Status is not Days, add Status in your filter instead of Days.

Weight(Days) = CALCULATE(SUM('Project_dim'[Days]), ALLEXCEPT('Project_dim', 'Project_dim'[Status]))

2. This code will calculate the current number of days as a percentage of the total filtered by the number of days.

WTD = SUM('Project_dim'[Days])/ [Weight(Days)]

3. WTD and COUNT value will change by your visual format. If you need a certain value, add some filter in your count code.

WTD AVG = [WTD] * COUNT('Project_dim'[Project ID])

It is better for you to show us a easy sample with the same data model like yours, and show us the result you want. This will make it easier for us to know your calculate logic.

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.

[bikelley]

@Anonymous 

Thank you so much for the help and explation. This should work for me. This is awesome.