Matrix Background Color Based on Field Value: Can it be a Gradient?

[Anonymous]

Hi all, I've been trying to add conditional background colors to a Matrix visual.

This is what my visual looks like (Excel representation):

Excel representation of my data

My dataset has the Business Divisions as coming from one Column and Revenue and Cost as being in the SAME "Values" column. The Cost-Income Ratio is a measure calculated by doing Cost/Revenue.

This is what my dataset looks like: 

Business Division	Year	Type	Value
Mangement Consulting	2020	Revenue	100
Mangement Consulting	2021	Revenue	105
Mangement Consulting	2022	Revenue	120
Technology Consulting	2020	Revenue	320
Technology Consulting	2021	Revenue	290
Technology Consulting	2022	Revenue	240
Tax Consulting	2020	Revenue	80
Tax Consulting	2021	Revenue	75
Tax Consulting	2022	Revenue	60
Audit	2020	Revenue	50
Audit	2021	Revenue	60
Audit	2022	Revenue	65
Mangement Consulting	2020	Cost	80
Mangement Consulting	2021	Cost	70
Mangement Consulting	2022	Cost	85
Technology Consulting	2020	Cost	140
Technology Consulting	2021	Cost	190
Technology Consulting	2022	Cost	270
Tax Consulting	2020	Cost	40
Tax Consulting	2021	Cost	45
Tax Consulting	2022	Cost	55
Audit	2020	Cost	90
Audit	2021	Cost	40
Audit	2022	Cost	45

I then used this measure to get the Cost-Income Ratio:

Cost/Income Ratio = 
VAR CostVal = 
CALCULATE(
SUM('Data'[Value]), 
FILTER('Data', CONTAINSSTRING('Data'[Type], "cost"))
)
VAR RevenueVal = 
CALCULATE(
SUM('Data'[Value]), 
REMOVEFILTERS('Data'[Type]),
-- This is a separate table with the headers names stored as a slicer and connected to 'Data' on the Type field. Did this because the "Ratio" field needs to be on the matrix, but is non-existent in the data.
ALL('Data_Headers'),
CONTAINSSTRING('Data'[Type], "revenue")
)
RETURN
IFERROR(CostVal/RevenueVal,0)

This table is used for the headers of the Matrix, with the Header Conenction field being 1-many with 'Data' Type field:

Header Name	Header Connection
Revenue	Revenue
Cost	Cost
Ratio	Cost

I used a Switch measure on the Matrix to chose [Value] when the SELECTEDVALUE('Header Name') is not "Ratio", and to show the [CI Ratio] when the SELECTEDVALUE() contains "Ratio".

Finally, I used this measure to have the background be RED when the Cost-Income Ratio is above 1.0 and green when it is below:

Background Color: Ratio Table = 
SWITCH(
TRUE,
CONTAINSSTRING(SELECTEDVALUE('Data'[Header Name]), "Ratio"),
IF('Data'[Cost/Income Ratio] >= 1.0, "#ffc7ce", "#c6efce")
)

The issue is that a Cost-Income ratio of 90% is equally as green as a Cost-Income Ratio of 10%. However, 10% CI is way better than 90%. Same goes for CI Ratios over 100%. Is there a way to add a gradient to both sides of this? Where the larger the value, the deeper the hue (it's either better or worse as it moves away from 1.00 CI Ratio). I've been having trouble because since all of this is coming from 1 field/series, but I only want to show a gradient under the "Ratio" column, not under the "revenue" and "cost" column, it means that I have to return the background color as a field value. Is there some sort of way I can make this color field value be a function of what's in the 'Data' field?

I would appreciate any help on this. Thank you! 

[lbendlin · Accepted solution]

Don't try to make Power BI behave like Excel. They have different strengths.

see attached.