Conditional formatting for visual

[JoshP11]

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.

Category	Height
Above Average	> = Average + Standard Deviation
High Average	Between Average + Standard Deviation & Average + 0.5 * Standard Deviation
Low Average	Between 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

[parry2k]

@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.

[parry2k]

@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]

Hi @parry2k 

Thank you for replying!

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

Thanks,

Josh

[JoshP11]

HI @parry2k 

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

Name	Date	Jump Height
NAMEONE	01/01/2024	40.3
NAMEONE	01/04/2024	38.35
NAMEONE	29/09/2024	40.05
NAMETWO	02/04/2024	37.5
NAMETWO	04/04/2024	39
NAMETWO	04/12/2024	42.34
NAMETHREE	01/01/2024	41.44
NAMETHREE	20/01/2024	37.9
NAMETHREE	27/01/2024	36.4
NAMETHREE	15/02/2024	39.25
NAMETHREE	22/04/2024	41.24
NAMEFOUR	26/04/2024	41.5
NAMEFOUR	11/05/2024	40
NAMEFIVE	18/09/2024	39.45
NAMEFIVE	21/09/2024	38.36
NAMEFIVE	27/09/2024	38.7
NAMESIX	02/03/2024	40.13
NAMESIX	19/04/2024	42.22
NAMESEVEN	22/04/2024	35.45
NAMEEIGHT	09/09/2024	38.4
NAMEEIGHT	28/09/2024	39.8
NAMEEIGHT	29/09/2024	40.25
NAMENINE	05/05/2024	44.45
NAMENINE	08/09/2024	43.1

[v-tianyich-msft · Accepted solution]

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.

[JoshP11]

Thank you @v-tianyich-msft  - that worked perfectly!