Forum Discussion

JoshP11's avatar
JoshP11
Helper II
1 year ago
Solved

Conditional formatting for visual

Hello,

 

I'm struggling to add some conditional formatting to a visual.

 

I have a filter for Name, and then a filter for Date, this then dynamically changes another visual which displays Jump Height.

 

 

I then want the colour of the column to change based on the below.

 

CategoryHeight
Above Average> = Average + Standard Deviation
High AverageBetween Average + Standard Deviation & Average + 0.5 * Standard Deviation
Low AverageBetween Average + 0.5 * Standard Deviation & Average - 0.5 * Standard Deviation
Below Average< = Average - Standard Deviation

 

For example, depending on what 41.8 is equal to, the colour will change, for example if it's above average, then the column in the visual will show green.

 

Does anyone know a way of doing this? I do also have those calculations already calculated in a seperate table I have loaded in if thats helps.

 

Hopefully this makes sense and I would appreiate any help provided.

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi JoshP11 ,

     

    I made simple samples and you can check the results below:

    Average = AVERAGEX(ALL('Table'),[Jump Height])

Standard Deviation = CALCULATE(STDEV.P('Table'[Jump Height]),ALL('Table'))

Measure = var ave =[Average]
VAR sta = [Standard Deviation]
RETURN
SWITCH(
    TRUE(),
    MAX('Table'[Jump Height]) >= ave + sta, "Above Average",
    MAX('Table'[Jump Height]) >= ave + 0.5 * sta && MAX('Table'[Jump Height]) < ave + sta, "High Average",
    MAX('Table'[Jump Height]) >= ave - 0.5 * sta && MAX('Table'[Jump Height]) < ave + 0.5 * sta, "Low Average",
    MAX('Table'[Jump Height]) <= ave - sta, "Below Average",
    "Other"
)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    JoshP11 you can create a measure for the color and use that in the condition formatting using field value"

     

    Color = 
//this is where you color logic will go
IF ( X > Y, "Red", "Green" )
    • JoshP11's avatar
      JoshP11
      Helper II
      1 year ago

      Hi parry2k 

       

      Thank you for replying!

       

      I'm pretty new at PBI, how would I write the logic?

       

      Thanks,

      Josh

      • JoshP11's avatar
        JoshP11
        Helper II
        1 year ago

        HI parry2k 

         

        It's basically just this in the data so far whilst Ive been trying to work out how to do it.

         

        NameDateJump Height
        NAMEONE01/01/202440.3
        NAMEONE01/04/202438.35
        NAMEONE29/09/202440.05
        NAMETWO02/04/202437.5
        NAMETWO04/04/202439
        NAMETWO04/12/202442.34
        NAMETHREE01/01/202441.44
        NAMETHREE20/01/202437.9
        NAMETHREE27/01/202436.4
        NAMETHREE15/02/202439.25
        NAMETHREE22/04/202441.24
        NAMEFOUR26/04/202441.5
        NAMEFOUR11/05/202440
        NAMEFIVE18/09/202439.45
        NAMEFIVE21/09/202438.36
        NAMEFIVE27/09/202438.7
        NAMESIX02/03/202440.13
        NAMESIX19/04/202442.22
        NAMESEVEN22/04/202435.45
        NAMEEIGHT09/09/202438.4
        NAMEEIGHT28/09/202439.8
        NAMEEIGHT29/09/202440.25
        NAMENINE05/05/202444.45
        NAMENINE08/09/202443.1
  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    JoshP11 it is a bit hard to explain, It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.