How to calculate weighted average based on data from different tables?

[wsspglobal]

Hi 

I have GDP data per country, and would like to calculate the weight each country within a Geographic Group has, and then multiply the wieghts to corresponding scores per country, ultimately get a weighted average score per Geographic Group. How do I calculate the weights in a table (table view)? I thought about using

SUMMARIZECOLUMNS('Table'[Geographic Group], "Total GDP", 'Table'[GDP] ), to get a the sum GDP of each geogrpahic groupt to get the denominator, but this code was not working, and not sure I'm on the right path.

Thanks!

[v-xicai]

Hi  @wsspglobal ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @wsspglobal ,

You may create column or measure like DAX below to get weighted rate.

Measure: Weighted rate= DIVIDE(SUM(Table1[GDP]),CALCULATE(SUM(Table1[GDP]),ALL(Table1)))
 
Column: Weighted rate= DIVIDE(CALCULATE(COUNT(Table1[GDP]),FILTER(ALLSELECTED(Table1),Table1[Country]=EARLIER(Table1[Country])&&Table1[Geographic Group]=EARLIER(Table1[Geographic Group]))),SUM(Table1[GDP]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.