Hi there!
I am having trouble getting a new conditional column on my Power BI Dashboard.
I have the following columns in my data:
- Name and Surname of individuals
- Question (one per row, and they would appear more than once when different people answer. They need to answer all questions)
- Responses (which go from 1 to 4 in number)
I have already calculated the Average Response of the column Responses with the AVERAGE DAX function.
Now, what I need to do is divide my groups into three percentiles (33,33% each equally) that are calculated based on the Average Response. This would then need to categorize each answer into one Percentile.
Example goes like this:
I got 7 people interviewed. Each answered 19 questions.
Average Response for all questions was 2,6.
The 33,33% of that average response is 0,85, which we will call THIRD now.
I need to create a conditional column in my table that goes by saying: "if RESPONSE < THIRD , "Negation", if ( RESPONSE < THIRD * 2, "Accept", "Impulse"))
This would mean that no person would get "Negation" (as the lowest answer is 1), if they answered 1 they would get "Accept" and if they answered 2, 3 or 4 they would get "Impulse".
Problem is:
- I cannot input a fluctuating value into a Conditional Column when I create ir from Power Query Editor. I can only input a value or a column, but my THIRD will need to fluctuate once I get more answers and the Average Response changes.
- When I create a new column from Power BI Directly (lwith DAX like I have wrote just above), I always get the "Impulse" result, no matter what the response was (especially with the ones where the response was 1).
Is there anything I'm doing wrong? How can I solve this issue in an easy way?
Thank you very much!
