Conditionnal formatting on matrix visuel and percentage of variation

[psorel]

Hello, I have a matrix visualization containing data from month M and previous months up to M-5 in columns .
I'm creating M-1/M-2, M-2/M-3 variance measurements in %.
Is it possible to apply visual formatting to these variance columns according to the following criteria:

- red variation +/- 50%
- orange variation +/- 25%
- green +/- 10%

Thank You

[parry2k]

@psorel, glad to hear. When you get a chance, please click "Accept as solution" so that others can find this post.

[psorel]

@DataNinja777 @parry2k  
Thank you for your work. It's perfect !!!

[psorel]

Hi, 
Thank you, i will try your solution. I comeback later.

[v-hjannapu]

Hi @psorel,

I would also take a moment to thank @DataNinja777  , @parry2k  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[DataNinja777 · Accepted solution]

Hi @psorel ,

In order to conditionally format the % variance, you can create a previous month measure like below:

Previous Month = calculate([Current Month],DATEADD('Date'[Date],-1,Month))

Then, create a variance measure like below:

Variance = divide([Current Month],[Previous Month])

Finally, you can add a color measure like below:

Variance Color = 
VAR r   = [Variance]                  -- Current / Previous
VAR dev = IF( ISBLANK(r), BLANK(), ABS( r - 1 ) )  -- distance from 1.0
RETURN
SWITCH(
    TRUE(),
    ISBLANK(dev), BLANK(),
    dev >= 0.50, "#FF0000",   -- red  (±50% or more)
    dev >= 0.25, "#FFA500",   -- orange (±25–50%)
    dev >= 0.10, "#00B050",   -- green (±10–25%)
    "#D9D9D9"                 -- gray (within ±10%)
)

 You can use this color measure to conditionally format the [Variance] and [Variance-1] measures, and the resultant conditional formatting is as shown below:  
([Variance-1] was added to clearly demonstrate the conditional coloring matches the conditions set because it is not clear to see that from [Variance] measure alone.)

I attach a pbix file for your reference. 

Best regards,

[parry2k]

@psorel you can create a measure for color formatting and use it as field value in conditional formatting:

Feel free to tweak the code as per your business needs.

Color =
VAR __Variance = [Variance % Measure]
RETURN
SWITCH ( 
   TRUE (),
   __Variance >= .1, "Green",
   __Variance >= .25, "Orange",
   "Red"
)