Forum Discussion
Helper ITwo filters for the same column
Super UserSince you are multiplying by zero that is the correct result of the calculation. What would be your expected outcome? What if you shifted your values by 2? 1 becomes 3, 0 becomes 2, -1 becomes 1 ?
Helper Ilbendlin Thank you for your response!
I don't want the calculation to take place on the row of 'Table'[Weighted Answers] where 'Table'[Questions] =SelectedQuestion2. 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.
Examples:
(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
(2) SelectedQuestion1 = "Liquidity problems intensity"
SelectedQuestion2 = "Expected change in demand in the next 6 months"
SelectedPrice = 0
The expected result should be: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759
- lbendlin
I don't see a "SelectedPrice" column in your sample data?
- atziovara
lbendlin
As mentioned on my original post - inside the measure Weighted Average:
VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])
This means that SelectedPrice is a slicer that takes its values from the column [Answers] of table 'SelectedCharacteristics'.SelectedCharacteristics is a disconnected table that has been created from summarizing the original table, 'TableBI':
SelectedCharacteristics =SUMMARIZE( 'TableBI','TableBI'[Questions],'TableBI'[Answers])
Hi atziovara ,
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', the field [Questions] of table "SelectedCharacteristics" and the field [Answers] of table "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:
(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
Hello Anonymous ! Thank you very much for your answer!
Let me analyse the example of the calculation I am trying to perform:(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
I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 1.
The Questions & Answers in RED are the ones who check the conditions of the filtering for each Company ID. They ensure that the weighted average of Liquidity problems intensity for each company and wave will be calculated for SelectedQuestion2 = "Expected change in demand in the next 6 months" & SelectedPrice = 1.
The weighted average is calculated as the sum of the Weighted Answers of Liquidity problems intensity (yellow background) divided by the sum of the Weights of Liquidity problems intensity (green background). It is based on the typical mathematical formula:
Weighted Mean = Σ (wi * xi) / Σwi
Therefore, the expected result is: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5
(2) SelectedQuestion1 = "Liquidity problems intensity"
SelectedQuestion2 = "Expected change in demand in the next 6 months"
SelectedPrice = 0
The logic is the same as above.
The expected result is: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759