Advanced Conditional Formatting based on Variable

[IPGeorgiev]

Hi All,

I have a bit complex goal I want to achieve, not sure if its possible at all.

Here a pbix file: https://drive.google.com/file/d/1BOvuaF2RGc0NGvtAtxae2He2nyVE5hOT/view?usp=sharing 

So in general in the CSAT table we have data which we use to calculate CSAT%. Then I want to create a matrix as the one in the pbix file.

So it has as rows Month and then Channel. Then as value we will have the respective CSAT% for the given Month&Channel.

For each Month&Channel we have a different goal and different network result - those are visible in the table targets.

So I want to apply the following logic:

IF -  Network Result < CSAT% < Target  - have a yellow background

IF - CSAT%<Network Results&Target - Red background 

IF - CSAT%> from both Target & Network - Green background.

So here the target and network results will be variables taken from the Targets table based on the month and channel. 

Would you be able to help on this one?

Many, many thanks in advance!!

Best regards,

Ivan

[jdbuchanan71 · Accepted solution]

@IPGeorgiev 

I made a few tweaks to your model.

• Added a date table that can be used to filter both CSAT and Targets
• Replaced the Channels table with one generated from the data so all the Channels will always be in there.

Then I added some measures to use for Network and Target.

Max CSAT Network = MAX ( Targets[CSAT Network] )

Max CSAT Target = MAX ( Targets[CSAT Target] )

And those we can use in the formatting measure

Format = 
VAR _csat = [CSAT%]
VAR _network = [Max CSAT Network]
VAR _target = [Max CSAT Target]
RETURN
SWITCH(
    TRUE(),
    _network < _csat && _csat < _target, "#F9D087", --yellow
    _csat < _network && _csat < _target, "#F78272", --red
    _csat > _network && _csat > _target, "#84C28A" --green
)

This measure then gets used to set the background conditional format for each of the columns.

I only applied it to the first column in my example:

I have attached my update of your file for you to look at.

[IPGeorgiev]

You are genius!!! Many, many thanks!

[amitchandak]

@IPGeorgiev , You can create a complex color measure and use that in conditional formatting using field value option

example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

refer my video for steps : https://www.youtube.com/watch?v=RqBb5eBf_I4