Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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!

 

 

Need Help
Show and Tell
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    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.

     

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    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.

     

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  Anonymous ,

     

    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