Conditional formatting based on multiple other values

[ls784]

Hello

I'm wondering if it's possible to conditionally format cells based on more than one other value.

Ideally I'd like to conditionally format a cell to show counts of each of the different response rates. I've got a lot of rows in this data so I dont want to have to create a separate bar visual for each line.

Any advice would be appreciated!

[Anonymous · Accepted solution]

Hi @ls784 , hello danextian and divyed, thank you for your prompt reply!

Create a calcualted column as shown as shown below:

Column = VAR __Total = ('Table'[Strongly Disagree]) + ('Table'[Disagree]) +('Table'[Neither Agree nor Disagree]) + ('Table'[Agree]) + ('Table'[Strongly Agree])

VAR __Width1 = DIVIDE(('Table'[Strongly Disagree]), __Total, 0) * 1000
VAR __Width2 = DIVIDE(('Table'[Disagree]), __Total, 0) * 1000
VAR __Width3 = DIVIDE(('Table'[Neither Agree nor Disagree]), __Total, 0) * 1000
VAR __Width4 = DIVIDE(('Table'[Agree]), __Total, 0) * 1000
VAR __Width5 = DIVIDE(('Table'[Strongly Agree]), __Total, 0) * 1000

VAR __Start2 = __Width1
VAR __Start3 = __Width1 + __Width2
VAR __Start4 = __Width1 + __Width2 + __Width3
VAR __Start5 = __Width1 + __Width2 + __Width3 + __Width4

VAR __Color1 = "Red"
VAR __Color2 = "Orange"
VAR __Color3 = "Gray"
VAR __Color4 = "LightGreen"
VAR __Color5 = "Green"

VAR __header = "data:image/svg+xml;utf8," &
               "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' width='1000' height='200'>"
VAR __footer = "</svg>"

VAR __Rect1 = "<rect x='0' y='0' width='" & __Width1 & "' height='200' style='fill:" & __Color1 & ";stroke-width:0;' />"
VAR __Rect2 = "<rect x='" & __Start2 & "' y='0' width='" & __Width2 & "' height='200' style='fill:" & __Color2 & ";stroke-width:0;' />"
VAR __Rect3 = "<rect x='" & __Start3 & "' y='0' width='" & __Width3 & "' height='200' style='fill:" & __Color3 & ";stroke-width:0;' />"
VAR __Rect4 = "<rect x='" & __Start4 & "' y='0' width='" & __Width4 & "' height='200' style='fill:" & __Color4 & ";stroke-width:0;' />"
VAR __Rect5 = "<rect x='" & __Start5 & "' y='0' width='" & __Width5 & "' height='200' style='fill:" & __Color5 & ";stroke-width:0;' />"

VAR __SVG = __header & __Rect1 & __Rect2 & __Rect3 & __Rect4 & __Rect5 & __footer
RETURN
    __SVG

Result for your reference:

Best regards,

Joyce

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

[ls784]

Ah this perfect thank you, I never would have got there by myself

[danextian]

You mean you want to have this multi-colored databar for each row?

[ls784]

Yep! Do you think this is possible?

[divyed]

Hello @ls784 ,

You can create a dax measure and use it for conditional formatting. Dax measure can use multiple fileds and values to give you desired outcome.

Step 1 : Create a dax measure as per need

Step 2 : Use this dax measure for conditionally format your values

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Cheers

[ls784]

Hello

The problem is I don't know the DAX measure that will give me the result I need? Can't figure out how to get a measure to bring back the length of bars and the colours for each line.

Thanks