Forum Discussion

medonication's avatar
medonication
Helper I
5 years ago

Weighted Average Score

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!

 

Need Help
Tips and Tricks

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • medonication's avatar
      medonication
      Helper I
      5 years ago

      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...

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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's avatar
      medonication
      Helper I
      5 years ago

      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