Home » Change matrix background color based on value

Change matrix background color based on value

In this article you will see how to change the background color for specific rows in a matrix based on values in column.

Here, we have a matrix which displays the Total sales by segment & year.




Lets change the background color for specific rows based on values in column.

Assume that, we want to change the row background color based on Segment values, for “Channel Partners” it should be Light blue, for “Enterprise” it should be Yellow, for “Midmarket” it should be an Orange.

Now we will create a column which will return the color based on value in a column.

Go to filed pane, right click on table and select New column from context menu.

Now write a following DAX, it uses switch function which return hexacode for color  when expression match the corresponding segment value.

ChangeColor =
SWITCH (
financials[Segment],
"Channel Partners", "#ADD8E6",
"Enterprise", "#FFFF00",
"Midmarket", "#FFA500"
)
After that,  right click on Values than navigate to Conditional formatting than select Background color.



After that a Background color window opens as shown below.
In Format by option, select Field value.
In Apply to option, select Values only.
In Based on field option, select column ChangeColor.
In Summarization option, it allows you the selection of First or Last value. By default it summarize the field value by First.
After that, click on Ok button.
Once you done with this, you will see background color for rows has been changed for matching Segment values “Channel Partners”, “Enterprise” and “Midmarket”.
Similarly, you can also define color for remaining segments.
Lets change the color for segment “Government” to Green and “Small Business” to Red.
Lets make the changes in DAX as shown below.
ChangeColor =
SWITCH (
financials[Segment],
"Channel Partners", "#ADD8E6",
"Enterprise", "#FFFF00",
"Midmarket", "#FFA500",
"Government", "#93C47D",
"Small Business", "#FF0000"
)
Once you compile the DAX, you will see the background color for all rows has been changed.
Also Read..

 147 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.