How to have Dynamic black ground color expression ?

[Paulyeo11]

Hi All

I have below script work fine.

Background Color 1 =

VAR _variance_per_country =

CALCULATE ( [Variance %], ALLEXCEPT ( 'TABLE', 'TABLE'[BRAND_C] ) )

RETURN

SWITCH (

TRUE (),

_variance_per_country < 0.01, "#D64550",

_variance_per_country < 0.2, "#E8D166",

_variance_per_country <= 10, "#79FF00",

"#00000"

)

Only issue is BRAND_C , which is not dynmaic ,  meaning i need to create many similar expression for SEGMENT , SALES , etc.

Background Color 1 =

VAR _variance_per_country =

CALCULATE ( [Variance %], ALLEXCEPT ( 'TABLE', 'TABLE'[SEGMENT] ) )

RETURN

SWITCH (

TRUE (),

_variance_per_country < 0.01, "#D64550",

_variance_per_country < 0.2, "#E8D166",

_variance_per_country <= 10, "#79FF00",

"#00000"

)

Background Color 1 =

VAR _variance_per_country =

CALCULATE ( [Variance %], ALLEXCEPT ( 'TABLE', 'TABLE'[SALES_PEOPLE] ) )

RETURN

SWITCH (

TRUE (),

_variance_per_country < 0.01, "#D64550",

_variance_per_country < 0.2, "#E8D166",

_variance_per_country <= 10, "#79FF00",

"#00000"

)

[v-stephen-msft · Accepted solution]

Hi @Paulyeo11 ,

For having dynamic background color expression, you just use [Variance %] instead of 

CALCULATE ( [Variance %], ALLEXCEPT ( 'TABLE', 'TABLE'[BRAND_C] ) ).

And your switch expression may have a little problem.

The revised complete expression is as follows:

Background Color 1 =

VAR _variance_per_country =

[Variance %]

RETURN

SWITCH (

TRUE (),

_variance_per_country < 0.01 && _variance_per_country >= 0, "#D64550",

_variance_per_country < 0.2 && _variance_per_country >= 0.01, "#E8D166",

_variance_per_country <= 10 && _variance_per_country >=0.2, "#79FF00",

"#00000"

)

Best Regards,

Stephen Tao

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

[Paulyeo11]

Hi Stephen

Thank you very much

Paul