Conditional Formatting- Background color based on comparing the values of multiple columns

[third_hicana]

Hi. I just want to ask for help on how to create a measure to format the background color based on the lowest to the highest value of multiple columns.

Thank you for your help

[third_hicana]

Hi @v-weiyan1-msft 

Is there anyway to do it on a table instead of a matrix? columns that I need to include aside from FTEs. Thank you. 

[v-weiyan1-msft]

Hi @third_hicana ,

I am not sure if I understood your question correctly.
Based on your description and my understanding, I used the following data sample.

Please refer to the following steps:
1.Please try code as below to Create a Calcualted table.

Table 2 = DATATABLE(
                    "ColumnName",STRING,
                    "Order",INTEGER,
                    {
                        {"Est. FTE Q1",1},
                        {"Est. FTE Q2",2},
                        {"Est. FTE Q3",3},
                        {"Est. FTE Q4",4},
                        {"Est. FTE for Initiative Duration",5}
                    }
                )

2. Use the following code to create a Measure.

Measure = 
VAR Q1 = SELECTEDVALUE('Table'[Est. FTE Q1])
VAR Q2 = SELECTEDVALUE('Table'[Est. FTE Q2])
VAR Q3 = SELECTEDVALUE('Table'[Est. FTE Q3])
VAR Q4 = SELECTEDVALUE('Table'[Est. FTE Q4])
VAR Initiative_Duration = SELECTEDVALUE('Table'[Est. FTE for Initiative Duration])
RETURN
SWITCH(
    MAX('Table 2'[ColumnName]),
    "Est. FTE Q1",Q1,
    "Est. FTE Q2",Q2,
    "Est. FTE Q3",Q3,
    "Est. FTE Q4",Q4,
    "Est. FTE for Initiative Duration",Initiative_Duration
)

3.The fields in the visual object are as follows, right-click "Measure" and select "Conditional Formatting". Then do the following.

Result is as below.

Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[third_hicana]

Hi @lbendlin  Here's sample data. Thanks

BOP ID    BOP Title            Area           Department Code    Q1         Q2         Q3         Q4         Entire duration

B003	Project 1	Operations	1	0.33	0.7	0.67	1
B003	Project 1	Operations	2	0.07	2	0.27
B003	Project 1	Operations	3					3
B003	Project 1	Operations	4					1
B003	Project 1	Operations	5	1
B003	Project 1	Operations	6			0.7
B003	Project 1	Operations	7		1		0.33
B003	Project 1	Operations	8	0.5	2.5
B003	Project 1	Operations	9			0.25
B003	Project 1	Operations	10	1
B003	Project 1	Operations	11					1
B003	Project 1	Operations	12					2
B003	Project 1	Operations	13					2.5
B003	Project 1	Operations	14					3.2
B003	Project 1	Operations	15		1
B003	Project 1	Operations	16			0.5