Issue with filters and 0

[atziovara]

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

• Here are two examples of what I need my measure to calculate:
  

  (1) SelectedQuestion1 = "Liquidity problems intensity"

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

  SelectedPrice = 1

  The result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5
  (This is working!)

  (2) SelectedQuestion1 = "Liquidity problems intensity"

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

  SelectedPrice = 0

  The result should be: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759
  (This is NOT working!)

[some_bih]

Hi @atziovara must ask: why your weighter avg. is not simple weighted avg., meaning why 
not use something like SUMX(  'TableBI','TableBI'[Weights]*'TableBI'[Answers]) or similar?

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