Weighted Average Score

[medonication]

Dear PBI Community,

I need your help to achieve a task that took me too much time (which sound so complex but in reality it isn't)

I want to calculate the average score of a brand and show it in a bar chart ( X: Brand Y: Average Weighted Score Legend: Brand Attribute) That it 🙂

So the Table contains: Country - Vehicle Type (Light vehicle or heavy...) - Product - Brand - Brand Attribute - Score and the Population which we should use to weight. 

the user can use Country, Vehicle Type and Product to filter and should have a weighted score depending on countries he choosed.

Actually  my code looks like this:
Value weighted by VIO.2020 =
divide(SUMX('Structur','Structure'[Value]*'Structure'[Population]),SUMX('Structure','Structure'[Population]))

This code calculate very well but the problem here is some small brands existing in only one small country have always good score so they are on top of the list. Score of these brands needs to be divided by the total population choosen.

Also some brands are existing in only one countries - those need to be also weighted by the overall population from the countries choosen.

I hope i did explain my use case well and I will be so happy if someone could help me 🙂

Thank you very much in advance!

[medonication]

Is there any solution for that problem or should I just forget about it ?

[v-yingjl]

Hi @medonication ,

Have couple of problems want to know based on your description and the sample data screenshot:

"here is some small brands existing in only one small country have always good score so they are on top of the list. Score of these brands needs to be divided by the total population choosen" 

How to define the 'small brand' and 'small country? What did the population represent?

Maybe you can share more details about this issue and the calcualtion logic clearly for further discussion. You can also consider sharing a simple sample .pbix file without sesentive information.

Sample file and expected output would help tremendously.
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

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[medonication]

Hope it is clear...

[medonication]

How to define the 'small brand' and 'small country? What did the population represent?

Small Brands are brands existing in just few countries, those brands could have great scores in average, but if the user select all countries these brands should'nt be displayed on top, but need to be weighted using the Population of the countries in which they are existing. Big Brands are brands existing in lot of countries ( especially countries with higher Population)
Population = Vehicle Population

[amitchandak]

One way is to create Rank on Value weighted by VIO.2020 and Population and multiple then treat that as a score

[medonication]

Thank you very much Amitchandak for your answer. The score is a percentage and if a user filter one country, one vehicle type and one product I want to show the actual row value.
 I think the only difficulty here is to calculate the overall Population which divide the weighted score. To be concret this sumx in my code "SUMX('Structure','Structure'[Population])" needs some changes...

[amitchandak]

@medonication ,Can you share sample data and sample output in table format?

[medonication]

@amitchandak can you see the screenshot ?

[medonication]

this is a sample of the table i filtered two brands, two  countries and one attribute which should be a legend.
Last column is the population which is our weight and value is the score as you see in percent. 

Thanks for your efforts!!