Two filters for the same column

Question

&nbsp;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).&nbsp;My dataset is like this:&nbsp;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).&nbsp;I created a disconnected table "Selected Characteristics" as:&nbsp;SelectedCharacteristics&nbsp;=SUMMARIZE(&nbsp;'TableBI',&nbsp; &nbsp;&nbsp;'TableBI'[Questions],&nbsp; &nbsp;'TableBI'[Answers])&nbsp;I created the following measure:&nbsp;Weighted Average =VAR&nbsp;SelectedQuestion1&nbsp;=&nbsp;SELECTEDVALUE('TableBI'[Questions])VAR&nbsp;SelectedQuestion2&nbsp;=&nbsp;SELECTEDVALUE('SelectedCharacteristics'[Questions])VAR&nbsp;SelectedPrice&nbsp;=&nbsp;SELECTEDVALUE('SelectedCharacteristics'[Answers])RETURNDIVIDE(&nbsp; &nbsp;&nbsp;SUMX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'TableBI',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT(ISBLANK('TableBI'[Weighted Answers])) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT(ISBLANK('TableBI'[Weights])) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'TableBI'[Questions]&nbsp;=&nbsp;SelectedQuestion1&nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VALUES('TableBI'[Answers]),&nbsp;'TableBI'[Questions]&nbsp;=&nbsp;SelectedCharacteristic2,&nbsp;'TableBI'[Τιμή]&nbsp;=&nbsp;SelectedPrice,&nbsp;ALLEXCEPT('TableBI',&nbsp;'TableBI'[Company Index])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;'Table'[Weighted Answers]&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;SUMX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'TableBI',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT(ISBLANK('TableBI'[Weighted Answers])) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT(ISBLANK('TableBI'[Weights])) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'TableBI'[Questions]&nbsp;=&nbsp;SelectedCharacteristic1&nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VALUES('TableBI'[Answers]),&nbsp;'TableBI'[Questions]&nbsp;=&nbsp;SelectedCharacteristic2,&nbsp;'TableBI'[Answers]&nbsp;=&nbsp;SelectedPrice,&nbsp;ALLEXCEPT('TableBI',&nbsp;'TableBI'[Company Index])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'TableBI'[Weights]&nbsp; &nbsp; ))&nbsp;&nbsp;I added two slicers: one with Questions from TableBI and one with Questions and Answers from SelectedCharacteristics.I added a&nbsp;Clustered Column Chart&nbsp;that has&nbsp;Waves&nbsp;on its&nbsp;X-Axis&nbsp;and&nbsp;Weighted Average&nbsp;on its&nbsp;Y-Axis.&nbsp;The problem that I am encountering&nbsp;is that this formula is only working when selected Answer = 1 or Answer = -1, however,&nbsp;when Answer = 0 I get back no values at all on the graph.

lbendlin · Answer

Since you are multiplying by zero that is the correct result of the calculation.&nbsp; What would be your expected outcome?&nbsp; What if you shifted your values by 2?&nbsp; 1 becomes 3, 0 becomes 2, -1 becomes 1&nbsp; ?

atziovara · Answer

lbendlin&nbsp;Thank you for your response!&nbsp;I don't want the calculation to take place on the row of 'Table'[Weighted Answers] where 'Table'[Questions] =SelectedQuestion2.&nbsp;The row of 'Table'[Weighted Answers] which will be summed nees to be the same as the row where 'Table'[Questions] = SelectedCharacteristic1. Therefore, no multiplication by 0 should take place.&nbsp;Examples:(1) SelectedQuestion1 = "Liquidity problems intensity"SelectedQuestion2 = "Expected change in demand in the next 6 months"SelectedPrice = 1The expected result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5&nbsp;(2)&nbsp;SelectedQuestion1 = "Liquidity problems intensity"SelectedQuestion2 = "Expected change in demand in the next 6 months"SelectedPrice = 0The expected result should be: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759

lbendlin · Answer

I don't see a "SelectedPrice"&nbsp; column in your sample data?

anonymous · Answer

Hi&nbsp;atziovara&nbsp;,
Thanks for reaching out to us with your problem. Base on your description, there is one table named "TableBI". And you execute the following steps:
1. Create another disconnected calculated table "SelectedCharacteristics"
2. Create three sliceres, the applied fields on the slicers are from the field [Questions] of table 'TableBI',&nbsp;&nbsp; the field [Questions] of table "SelectedCharacteristics" and&nbsp; the field [Answers] of table&nbsp;"SelectedCharacteristics"
3. Create a measure to get the value --Here I'm not clear about the calculation logic, could you please explain more details on it with the examples and screenshot? Thank you.
For example:&nbsp;

(1) SelectedQuestion1 = "Liquidity problems intensity"
SelectedQuestion2 = "Expected change in demand in the next 6 months"
SelectedPrice = 1
The expected result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

According to the sample data and slicers, it will return the below data.

Why the expected result not be: [0+(-0,001)+(-0,0057)]/[0,006 + 0,001 +0,0057]?
Best Regards

atziovara · Answer

lbendlin&nbsp;As mentioned on my original post - inside the measure Weighted Average:VAR&nbsp;SelectedPrice&nbsp;=&nbsp;SELECTEDVALUE('SelectedCharacteristics'[Answers])This means that SelectedPrice is a slicer that takes its values from the column [Answers] of table 'SelectedCharacteristics'. &nbsp;SelectedCharacteristics&nbsp;is a disconnected table that has been created from summarizing the original table, 'TableBI':SelectedCharacteristics&nbsp;=SUMMARIZE(&nbsp;'TableBI',&nbsp; &nbsp;&nbsp;'TableBI'[Questions],&nbsp; &nbsp;'TableBI'[Answers])

Forum Discussion

Two filters for the same column

6 Replies

Related content

Slicer for filtering several columns

Apply data to all rows in a filtered column

Adding filters to a calculated column

Filter

odata Report Level Filters Multiple Filters Same Column

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