Forum Discussion
Helper IWeighted 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 Liquidity Problems Intensity for companies who expect that their Demand in the next 6 months will increase (Question= "Expected change in demand in the next 6 months", Answer = 1). My dataset is like this:
Table Name: TableBI
| Wave | Company Index | Company ID | Weights | Questions | Answers | Weighted Answers | Periods |
| 1 | 1 | 1-1 | 0,006 | Demand change compared to the previous 6 months | -1 | -0,006 | 2022 H2 |
| 1 | 1 | 1-1 | 0,006 | Expected change in demand in the next 6 months | 1 | 0,006 | 2023 H1 |
| 1 | 1 | 1-1 | 0,006 | Liquidity problems intensity | 1 | 0,006 | 2022 H2 |
| 1 | 1 | 1-1 | 0,006 | Expected change in employment | 0 | 0 | 2023 H1 |
| 1 | 2 | 1-2 | 0,001 | Demand change compared to the previous 6 months | 1 | 0,001 | 2022 H2 |
| 1 | 2 | 1-2 | 0,001 | Expected change in demand in the next 6 months | 1 | 0,001 | 2023 H1 |
| 1 | 2 | 1-2 | 0,001 | Liquidity problems intensity | -1 | -0,001 | 2022 H2 |
| 1 | 2 | 1-2 | 0,001 | Expected change in employment | 1 | 0,001 | 2023 H1 |
| 1 | 3 | 1-3 | 0,0057 | Demand change compared to the previous 6 months | 1 | 0,0057 | 2022 H2 |
| 1 | 3 | 1-3 | 0,0057 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H1 |
| 1 | 3 | 1-3 | 0,0057 | Liquidity problems intensity | 0 | 0 | 2022 H2 |
| 1 | 3 | 1-3 | 0,0057 | Expected change in employment | 0 | 0 | 2023 H1 |
| 2 | 1 | 2-1 | 0,006 | Demand change compared to the previous 6 months | 1 | 0,006 | 2023 H1 |
| 2 | 1 | 2-1 | 0,006 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H2 |
| 2 | 1 | 2-1 | 0,006 | Liquidity problems intensity | 0 | 0 | 2023 H1 |
| 2 | 1 | 2-1 | 0,006 | Expected change in employment | 0 | 0 | 2023 H2 |
| 2 | 2 | 2-2 | 0,001 | Demand change compared to the previous 6 months | 1 | 0,001 | 2023 H1 |
| 2 | 2 | 2-2 | 0,001 | Expected change in demand in the next 6 months | 1 | 0,001 | 2023 H2 |
| 2 | 2 | 2-2 | 0,001 | Liquidity problems intensity | -1 | -0,001 | 2023 H1 |
| 2 | 2 | 2-2 | 0,001 | Expected change in employment | 1 | 0,001 | 2023 H2 |
| 2 | 3 | 2-3 | 0,0057 | Demand change compared to the previous 6 months | 1 | 0,0057 | 2023 H1 |
| 2 | 3 | 2-3 | 0,0057 | Expected change in demand in the next 6 months | 0 | 0 | 2023 H2 |
| 2 | 3 | 2-3 | 0,0057 | Liquidity problems intensity | -1 | -0,0057 | 2023 H1 |
| 2 | 3 | 2-3 | 0,0057 | Expected change in employment | 1 | 0,0057 | 2023 H2 |
*Weighted Answers is calculated as: Weights * Answers
**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).
- I created a disconnected table "Selected Characteristics" as:
- I created the following measure:
- I added two slicers: one with Questions from TableBI and one with Questions and Answers from SelectedCharacteristics.
- I added a Clustered Column Chart that has Waves on its X-Axis and Weighted Average on its Y-Axis.
- The problem that I am encountering is that this formula is only working when selected Answer = 1 or Answer = -1, however, when Answer = 0 I get back no values at all on the graph.
5 Replies
atziovara@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.
