Forum Discussion

atziovara's avatar
atziovara
Icon for Helper I rankHelper I
2 years ago
Solved

Weighted Average with double filter Issue

I want to compute the Weighted Average for one of the following Questions, based on the value of the Answer to another Question. For example, I would like to compute the Weighted Average of the Liqu...
Need Help
  • some_bih's avatar
    some_bih
    2 years ago

    Hi atziovara Please check enclosed file two different tabs for two scenarios.

    I created 6 different measures just for debug (it could be less number of measures).

    Results are as in your example

    Part 1 ratio = 0,50 and Part 2 ratio = -0,3276

    Picture below for Part 2.

     

atziovara's avatar
atziovara
Icon for Helper I rankHelper I
2 years ago

@some_bih Thank you for your response!

The formula for the Weighted Averagethat I am using is a simple one!

 

It just sums the weighted answers (weights * answers) and divides the total by the sum of the weights.  I am using the typical mathematical formula: 
Weighted Mean = Σ (wi * xi) / Σwi


The filters that I am trying to use, which are neccesarry for drawing conclusions for business purposes, are creating the issue. I need to compute not only the Weighted Average for all the Questions posed to the companies, but I also for special cases, like "what is the weighted average of Liquidity problems intensity, for companies who expect that their demand will increase/stay stable in the next semester"?

 

My measure is working perfectly for all cases except when SelectedPrice = 0.

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
2 years ago

Hi atziovara weight avg. are in your case standard, which is good.

So in short, you need weighted avg. for each question (as separate measure)?

  • atziovara's avatar
    atziovara
    Icon for Helper I rankHelper I
    2 years ago

    Hello some_bih !

     

    Thank you for your response!

     

    Let me give you two examples of what I need to calculate: 

     

    (1) Let's assume that I,the user, choose:

    SelectedQuestion1 = "Liquidity problems intensity"

    SelectedQuestion2 = "Expected change in demand in the next 6 months"

    SelectedPrice = 1

     

    In this case I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 1. 

     

    The Questions & Answers in RED are the ones who check the conditions of the filtering for each Company ID. They ensure that the weighted average of Liquidity problems intensity for each company and wave will be calculated for SelectedQuestion2 = "Expected change in demand in the next 6 months" & SelectedPrice = 1. 

     

    The weighted average is calculated as the sum of the Weighted Answers of Liquidity problems intensity (yellow background) divided by the sum of the Weights of Liquidity problems intensity (green background). It is based on the typical mathematical formula:

    Weighted Mean = Σ (wi * xi) / Σwi

     

    Therefore, the expected result is: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

     

     

     

    (2) SelectedQuestion1 = "Liquidity problems intensity"

    SelectedQuestion2 = "Expected change in demand in the next 6 months"

    SelectedPrice = 0

     

    In this case I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 0. 

     

    The logic is the same as above. 

    The expected result is: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759