Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a conditional column based on a fluctuating value

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!

  • Anonymous  have you done an unpivot/ multiple tables to clean the data model first? See my webinar on survey data transformations if it helps: https://www.excelwithallison.com/MCT/news 

     

    Once you have the data model cleaned, you should be able to use a variable within calculated column to find THIRD and then use SWITCH (rather than nested IFs) to get what you need. 

1 Reply

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion

    Anonymous  have you done an unpivot/ multiple tables to clean the data model first? See my webinar on survey data transformations if it helps: https://www.excelwithallison.com/MCT/news 

     

    Once you have the data model cleaned, you should be able to use a variable within calculated column to find THIRD and then use SWITCH (rather than nested IFs) to get what you need.