Forum Discussion
Custom text based on average column values
Hello I'm new to Power BI, please excuse any faux pas.
Currently I have visuals (card and gauge) that display the average value of a numerical columns. Values in this column are between 0 and 10 and represent responses to questions. There are around 100 question and thus around 100 columns, Q1, Q2, Q3 etc.
Instead of displaying the average value for each question, is it possible to display text based on the average value? E.g.
*for average values 0 - 4, "negative" is displayed,
*for average values >4 - 6.5, "neutral" is displayed or
*for average values >6.5 - 10, "positive" is displayed
example data might be
| Q1 | Q2 | Q3 | Q4 | Q5 | Q6 |
| 2 | 7 | 8 | 5 | 6 | 7 |
| 4 | 5 | 9 | 1 | 6 | 6 |
| 5 | 5 | 7 | 3 | 4 | 9 |
Ideally I'd like dashboard cards (or other appropriate visual) to show "negative", "neutral", "positive", "negative", "neutral", "positive" for each of the corresponding questions.
Thank you in advance for any advice.
Hi Anonymous ,
That is possible using DAX but I would transform your raw data into a format that is easier for reporting. Below aer a sample formulas (as a measure). The second one explictily specifies the range
Text = VAR __AVG = CALCULATE ( AVERAGE ( 'Table'[Value] ) ) RETURN SWITCH ( TRUE (), __AVG > 6.5, "positive", __AVG <= 4, "negative", "neutral" )Text2 = VAR __AVG = CALCULATE ( AVERAGE ( 'Table'[Value] ) ) RETURN SWITCH ( TRUE(), __AVG >=0 && __AVG <=4, "negative", __AVG >4 && __AVG <=6.5, "neutral", __AVG >6.5 && __AVG <=10, "positive" )Please see attaced pbix for details
5 Replies
Hi Anonymous ,
That is possible using DAX but I would transform your raw data into a format that is easier for reporting. Below aer a sample formulas (as a measure). The second one explictily specifies the range
Text = VAR __AVG = CALCULATE ( AVERAGE ( 'Table'[Value] ) ) RETURN SWITCH ( TRUE (), __AVG > 6.5, "positive", __AVG <= 4, "negative", "neutral" )Text2 = VAR __AVG = CALCULATE ( AVERAGE ( 'Table'[Value] ) ) RETURN SWITCH ( TRUE(), __AVG >=0 && __AVG <=4, "negative", __AVG >4 && __AVG <=6.5, "neutral", __AVG >6.5 && __AVG <=10, "positive" )Please see attaced pbix for details
Thank you danextian This is really helpful. I've been able to create a measure "Q1_text" using DAX based on one of my columns (Q1) with the data as it is.
The first part of your response mentions transforming the data into a format that is more useful for reporting. I should have mentioned in my initial post that reponses to these questions come from different types of respondants (different departments) and that I want to be able to filter for each dept in my reporting. Can you see a way that I can transform my table to account for this, or will I need to add a text measure for each column?
Dept Q1 Q2 Q3 Q4 Q5 Q6 sales 2 7 8 5 6 7 marketing 4 5 9 1 6 6 sales 5 5 7 3 4 9 Hi Anonymous ,
If you follow the same format as your raw data is now, you'll end up creating a measure (that returns a text string) for each question. It will be probably be fine with just a few questions but very tedious and hard to to maintain for so many. Going back to the transformation I mentioned, there is a sample pbix in my previous response. The transformation users Power Query's unpivot function. These links will help you understand what I mean:
- https://support.microsoft.com/en-gb/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
- https://www.youtube.com/watch?v=ESap6ptV8fI
Hi,
Please find attached the solution file.
Hope this helps.
