Custom text based on average column values

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Please find attached the solution file.

Hope this helps.

[danextian · Accepted solution]

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

[Anonymous]

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

[danextian]

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

[Anonymous]

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.