Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    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

     

danextian's avatar
danextian
Super User
3 years ago

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:

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.