Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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).
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Hi @atziovara must ask: why your measure for wei.avg is not something like:
SUMX('Table', 'Table'[Weights]*'Table'[Answers])
Proud to be a Super User!
@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.
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)?
Proud to be a Super User!
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
User | Count |
---|---|
21 | |
20 | |
12 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |