Conditional formatting with different category

[steleo]

Hello everyone,

I have a problem with conditional formatting. 

I would like to apply custom conditional formatting to a stacked column / bar chart that has different categories on the x-axis. Example:

• If the average of the first category is = 100 then it colors the bar green; if >=50 and <100 then yellow, otherwise red.
• If the average of the second category is 100=green then t colors the bar green; if >=80 <100 thenn yellow, otherwise red.

Please not: the range changes between the two categories.

It is possible to do that in Power Query or in DAX?

[AlexisOlson · Accepted solution]

Since you can use a measure for conditional formatting you can create whatever arbitrary rules you'd like.

For example, you could write a measure similar to this

Formatting =
VAR _Cat = SELECTEDVALUE ( Table1[Category] )
VAR _Value = [AvgMeasure]
RETURN
    SWITCH (
        TRUE (),
        _Cat = "Category 1" && _Value = 100,                 1, // Green
        _Cat = "Category 1" && _Value >= 50 && _Value < 100, 2, // Yellow
        _Cat = "Category 1",                                 3, // Red
        _Cat = "Category 2" && _Value = 100,                 1, // Green
        _Cat = "Category 2" && _Value >= 80 && _Value < 100, 2, // Yellow
        _Cat = "Category 2",                                 3, // Red
        4
    )

and then set up your formatting rules like this: