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: TableBIWaveCompany IndexCompany IDWeightsQuestionsAnswersWeighted AnswersPeriods111-10,006Demand change compared to the previous 6 months-1-0,0062022 H2111-10,006Expected change in demand in the next 6 months10,0062023 H1111-10,006Liquidity problems intensity10,0062022 H2111-10,006Expected change in employment002023 H1121-20,001Demand change compared to the previous 6 months10,0012022 H2121-20,001Expected change in demand in the next 6 months10,0012023 H1121-20,001Liquidity problems intensity-1-0,0012022 H2121-20,001Expected change in employment10,0012023 H1131-30,0057Demand change compared to the previous 6 months10,00572022 H2131-30,0057Expected change in demand in the next 6 months002023 H1131-30,0057Liquidity problems intensity002022 H2131-30,0057Expected change in employment002023 H1212-10,006Demand change compared to the previous 6 months10,0062023 H1212-10,006Expected change in demand in the next 6 months002023 H2212-10,006Liquidity problems intensity002023 H1212-10,006Expected change in employment002023 H2222-20,001Demand change compared to the previous 6 months10,0012023 H1222-20,001Expected change in demand in the next 6 months10,0012023 H2222-20,001Liquidity problems intensity-1-0,0012023 H1222-20,001Expected change in employment10,0012023 H2232-30,0057Demand change compared to the previous 6 months10,00572023 H1232-30,0057Expected change in demand in the next 6 months002023 H2232-30,0057Liquidity problems intensity-1-0,00572023 H1232-30,0057Expected change in employment10,00572023 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: SelectedCharacteristics =SUMMARIZE( 'TableBI', 'TableBI'[Questions], 'TableBI'[Answers]) I created the following measure: Weighted Average =VAR SelectedQuestion1 = SELECTEDVALUE('TableBI'[Questions])VAR SelectedQuestion2 = SELECTEDVALUE('SelectedCharacteristics'[Questions])VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])RETURNDIVIDE( SUMX( FILTER( 'TableBI', NOT(ISBLANK('TableBI'[Weighted Answers])) && NOT(ISBLANK('TableBI'[Weights])) && 'TableBI'[Questions] = SelectedQuestion1 && CALCULATE( VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Τιμή] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index]) ) ), 'Table'[Weighted Answers] ), SUMX( FILTER( 'TableBI', NOT(ISBLANK('TableBI'[Weighted Answers])) && NOT(ISBLANK('TableBI'[Weights])) && 'TableBI'[Questions] = SelectedCharacteristic1 && CALCULATE( VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Answers] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index]) ) ), 'TableBI'[Weights] )) 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.

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

Helper 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 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:

SelectedCharacteristics =

SUMMARIZE( 'TableBI',

'TableBI'[Questions],

'TableBI'[Answers])

I created the following measure:

Weighted Average =

VAR SelectedQuestion1 = SELECTEDVALUE('TableBI'[Questions])

VAR SelectedQuestion2 = SELECTEDVALUE('SelectedCharacteristics'[Questions])

VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])

RETURN

DIVIDE(

SUMX(

FILTER(

'TableBI',

NOT(ISBLANK('TableBI'[Weighted Answers])) &&

NOT(ISBLANK('TableBI'[Weights])) &&

'TableBI'[Questions] = SelectedQuestion1 &&

CALCULATE(

VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Τιμή] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])

)

), 'Table'[Weighted Answers]

SUMX(

FILTER(

'TableBI',

NOT(ISBLANK('TableBI'[Weighted Answers])) &&

NOT(ISBLANK('TableBI'[Weights])) &&

'TableBI'[Questions] = SelectedCharacteristic1 &&

CALCULATE(

VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Answers] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])

)

'TableBI'[Weights]

)

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.

Need Help

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.

weight avg community.pbix34 KB

5 Replies

some_bih
Community Champion
2 years ago
Hi atziovara must ask: why your measure for wei.avg is not something like:

SUMX('Table', 'Table'[Weights]*'Table'[Answers])
- atziovara
  Helper 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
    Community 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)?
some_bih
Community Champion
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.

weight avg community.pbix34 KB

Forum Discussion

Weighted Average with double filter Issue

5 Replies

Related content

Calculating Weighted Average

Weighted Average DAX

Weighted Average Measure

Weighted average by month

Weighted Average