Forum Discussion
Custom text based on average column values
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
Just wanted to say thank you danextian - the tutorials you linked to, along with your guidance and pbix have solved my question and likely saved me hours of work.