Issue with filters and 0

atziovara — Mon, 04 Dec 2023 14:05:58 GMT

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!)

Re: Issue with filters and 0

some_bih — Mon, 04 Dec 2023 15:29:38 GMT

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?

Re: Issue with filters and 0

atziovara — Fri, 08 Dec 2023 14:49:11 GMT

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.

topic Re: Issue with filters and 0 in DAX Commands and Tips

Issue with filters and 0

Re: Issue with filters and 0

Re: Issue with filters and 0