Conditional Formatting on Matrix table based on measures that are calculated separately

[bkarki]

I have two data tables:

1. Columns : Location, Jan, Feb.... Dec. - this table is showing data of volumes for 2018 (which is my baseline)

2. Same as first table but here we can pick which year's data to show based on slicer so 2019,2020 etc. 

The purpose is to compare the baseline figures to figures of other years. 

Now i have measures which calculates the differences of the volumes compared to the baseline year. so i have the following measures:

• Diff 2019 - 2018
• Diff 2020 - 2018
• Diff 2021 - 2018 et etc...

when i have lets say 2019 picked in my slicer for table 2. I want to conditional format for 3 colors: e.g.

yellow: if difference is between 10% - 20% compared to baseline

orange: if difference is between 20% - 30% compared to baseline

red: if difference is more than 30% compared to baseline 

[v-zhangti]

Hi, @bkarki 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[bkarki]

here is the sample data. I want to view the source data which you can see in first picture as the third picture on Bi. Currently I am using Matrix. But I am not able to conditional format based on the percentage change as shown in last picutre. 

[CoreyP]

1. You should remodel your data to make it easier to compare figures and reduce the number of measures you have to write. You should unpivot your month columns in your baseline table so you have all your months in a single column, and can connect them to the date table same as your other tables. This would also allow you to dynamically change the baseline year if you ever wanted. 

2. You can create a measure to handle your conditional formatting. Like...

Conditional Formatting =

SWITCH(
TRUE(),

[Volume Delta] >= .10 && [Volume Delta] < .20 , "yellow" ,

[Volume Delta] >= .20 && [Volume Delta] < .30 , "orange" ,

[Volume Delta] >= .30 , "red" ,

"black"

)

You can use HEX code rather than words for the colors. Then just add this to the conditional formatting function and select By Field Value, and choose this measure.