DAX formula for conditional formatting column chart

[PowerRon]

I hope someone can help, cause sometimes propagation and filter context is difficult.

Regards
Ron 

[PowerRon]

Hi @Padycosmos thanx for you answer
I changed the measure as follows (by the way I replaced Amber by Purple, because for Amber it returs Blue, which seems odd)

Color =

var _differ = 1 - [KPI perc]

RETURN

SWITCH(

    TRUE(),

    [Check Against Target] >= 1, "Green",

    [Check Against Target] >= _differ && [Check Against Target] < 1, "Purple",

    [Check Against Target] <= _differ, "Red"

    )

Now it returns this


So it is already better but 202202 should be Red. 

The funny thing is, when clicking 202202 in the column chart and putting the measure Color in the table, it shows Red
That's really odd

[Padycosmos]

You have >=_differ in "Purple" and  <= _differ  in "Red", you need to remove one of the = . Then it should work

[PowerRon]

Hi @Padycosmos 

unfortunately no change
I changed 

    [Check Against Target] <= _differ, "Red"

into

    [Check Against Target] < _differ, "Red"

The tabel shows Red, te column chart Purple for 202202

[Padycosmos]

Could you please move "red" as the first statement, "purple" as the second and "green" as the last and try again?

[PowerRon]

No change @Padycosmos 

For each row I also selected _differ (Color_Differ)

[Padycosmos]

Hope this works

Color =
var _differ = 1 - [KPI perc]
RETURN
SWITCH(
TRUE(),
[Check Against Target] >= 1, "Green",
[Check Against Target] >= _differ && [Check Against Target] < 1, "Purple",
[Check Against Target] < _differ && [Check Against Target] < 1, "Red"
)

[Padycosmos]

If the data is not sensitive, could you please share the pbix file?

[PowerRon]

Hi @Padycosmos 

I can share the pbix file. Looking how to do that, but can't find it. How do I attach a pbix to a reply?

[Padycosmos · Accepted solution]

Here's the measure that works:

Colour3 =

var _differ = MAXX(ALLSELECTED('KPI Dimension'),1-[KPI perc])

var _target = [Check Against Target]

RETURN

SWITCH(

    TRUE(),

    _target >=1, "Green",

    _target < 1 && _target >=_differ, "Purple",

     _target < _differ , "Red"

    )

[Padycosmos]

You can put the file in your google drive and paste the link here. Please note to enable access to th file for anyone with the link

[PowerRon]

Hmm, ok , never used google drive. Will try later today. I can also send it personally if I have your email

[Padycosmos]

You may send it to padycosmos@gmail.com

[Padycosmos]

In Power BI, though the display format is %, the numbers are stored as decimals. Please make the following changes and try again

var _differ = 1- [KPI perc]

Replace both the 100s in the SWITCH() to 1