Weighted Average formula issue

[atziovara]

• I want to compute the Weighted Average for the following set of data:

Table Name: TableBI

Wave	Company ID	Weights	Questions	Answers	Weighted Answers	Periods
1	1	0,006	Demand change compared to the previous 6 months	-1	-0,006	2022 H2
1	1	0,006	Expected change in demand in the next 6 months	1	0,006	2023 H1
1	1	0,006	Liquidity problems intensity	1	0,006	2022 H2
1	1	0,006	Expected change in employment	0	0	2023 H1
1	2	0,001	Demand change compared to the previous 6 months	1	0,001	2022 H2
1	2	0,001	Expected change in demand in the next 6 months	1	0,001	2023 H1
1	2	0,001	Liquidity problems intensity	-1	-0,001	2022 H2
1	2	0,001	Expected change in employment	1	0,001	2023 H1
1	3	0,0057	Demand change compared to the previous 6 months	1	0,0057	2022 H2
1	3	0,0057	Expected change in demand in the next 6 months	0	0	2023 H1
1	3	0,0057	Liquidity problems intensity	0	0	2022 H2
1	3	0,0057	Expected change in employment	0	0	2023 H1
2	1	0,006	Demand change compared to the previous 6 months	1	0,006	2023 H1
2	1	0,006	Expected change in demand in the next 6 months	0	0	2023 H2
2	1	0,006	Liquidity problems intensity	0	0	2023 H1
2	1	0,006	Expected change in employment	0	0	2023 H2
2	2	0,001	Demand change compared to the previous 6 months	1	0,001	2023 H1
2	2	0,001	Expected change in demand in the next 6 months	1	0,001	2023 H2
2	2	0,001	Liquidity problems intensity	-1	-0,001	2023 H1
2	2	0,001	Expected change in employment	1	0,001	2023 H2
2	3	0,0057	Demand change compared to the previous 6 months	1	0,0057	2023 H1
2	3	0,0057	Expected change in demand in the next 6 months	0	0	2023 H2
2	3	0,0057	Liquidity problems intensity	-1	-0,0057	2023 H1
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 the following measure:

Weighted Average =

VAR SelectedChar = SELECTEDVALUE(TableBI[Questions])

VAR SelectedPrice = SELECTEDVALUE(TableBI[Answers])

VAR Numerator =

SUMX(

FILTER(

'TableBI',

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

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

'TableBI'[Questions] = SelectedChar &&

'TableBI'[Answers] = SelectedPrice

),

IF(

'TableBI'[Answers] = -1,

('TableBI'[Weighted Answers] * (-1)),

IF(

'TableBI'[Answers] = 0,

'TableBI'[Weights],

'TableBI'[Weighted Answers]

)

)

)

VAR Denominator =

SUMX(

FILTER(

'TableBI',

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

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

'TableBI'[Questions] = SelectedChar

),

'TableBI'[Weights]

)

RETURN

DIVIDE(Numerator, Denominator)

• More specifically, when Answer = -1 I want the first SUMX to be calculated for each row of Weighted Answers myltiplied by -1 (for which the filters are true of course), when Answer = 0 I want the first SUMX to be calculated for each row of Weights, and when Answer = 1, I want the first SUMX to be computed for each row of Weighted Answers. 
• The problem that I am encountering is that I only receive 1 as an answer for all Waves whether I select 0, 1, or -1 as an Answer on my slicer. It seems that for some reason the Numerator and the Denominator are the same, so one of the filters is not being applied.

[123abc]

Here's a modified version of your DAX measure:

Weighted Average =
VAR SelectedChar = SELECTEDVALUE(TableBI[Questions])
VAR SelectedPrice = SELECTEDVALUE(TableBI[Answers])

VAR Numerator =
SUMX(
FILTER(
TableBI,
NOT(ISBLANK(TableBI[Weighted Answers])) &&
NOT(ISBLANK(TableBI[Weights])) &&
TableBI[Questions] = SelectedChar &&
TableBI[Answers] = SelectedPrice
),
IF(
SelectedPrice = -1,
TableBI[Weighted Answers] * -1,
IF(
SelectedPrice = 0,
TableBI[Weights],
TableBI[Weighted Answers]
)
)
)

VAR Denominator =
SUMX(
FILTER(
TableBI,
NOT(ISBLANK(TableBI[Weighted Answers])) &&
NOT(ISBLANK(TableBI[Weights])) &&
TableBI[Questions] = SelectedChar
),
TableBI[Weights]
)

RETURN
DIVIDE(Numerator, Denominator)

[atziovara]

@123abc 
Thank you for your answer!

I am afraid the modified version that you sent me is still not working. I am now receiving 1 for all waves when Answer = 0 or 1, and -1 when Answer = -1.

It seems like the rows of the Numerator are not filtered correctly, and the Numerator is the same as the Denominator.

[FreemanZ]

hi @atziovara ,

not sure if i fully get you, try like:

1) feed the slicers with isolated calculated tables like:

answers = VALUES(TableBI[Answers])
questions = VALUES(TableBI[Questions])

2) plot a visual table with a slightly modified measure based on yours like:

Weighted Average = 
VAR SelectedChar = SELECTEDVALUE(Questions[Questions])
VAR SelectedPrice = SELECTEDVALUE(Answers[Answers])
VAR Numerator =
SUMX(
    FILTER(
        'TableBI',
        NOT(ISBLANK('TableBI'[Weighted Answers])) 
            &&NOT(ISBLANK('TableBI'[Weights])) 
            &&'TableBI'[Questions] = SelectedChar 
            &&'TableBI'[Answers] = SelectedPrice
    ),
    IF(
        'TableBI'[Answers] = -1,
        ('TableBI'[Weighted Answers] * (-1)),
        IF(
            'TableBI'[Answers] = 0,
            'TableBI'[Weights],
            'TableBI'[Weighted Answers]
        )
    )
)
VAR Denominator =
SUMX(
    FILTER(
        'TableBI',
        NOT(ISBLANK('TableBI'[Weighted Answers])) 
            &&NOT(ISBLANK('TableBI'[Weights])) 
            &&'TableBI'[Questions] = SelectedChar
    ),
    'TableBI'[Weights]
)
RETURN DIVIDE(Numerator, Denominator)

it worked like:

[atziovara]

@FreemanZ 
Thank you very much for your response! 

This partially resolves the issue! However, I have two more questions related to your answer:

(1) Some of the Answers of the original data do not have -1, 0, or 1 as values. They can take values varying from -100 to 100. Therefore, when I select a specific question in the relevant slicer of tables Question, the slicer with the Answers showcases all possible values and not the ones that are associated with the question I selected. 

Please note that it would not be preferable to just filter the Answers slicer, in case in the future the Weighted Average is expanded with more IF statements. I would like to be able to see the Answers that relate to the specific Question that is selected.

Is there a way to fix this?

(2) Also, if I want to compare all 3 of the Answers to a Question in one graph, would I need to make 2 more disconnected tables with 2 new measures (which will have the same formula as [Weighted Average] though), plus 2 more disconnected tables which will have Questions as their only column, and 2 more disconnected tables which will have Answers as their column?

Thank you very much in advance!