Forum Discussion

ls784's avatar
ls784
Helper I
1 year ago
Solved

Conditional formatting based on multiple other values

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!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

6 Replies

  • divyed's avatar
    divyed
    Super User
    1 year ago

    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's avatar
      ls784
      Helper I
      1 year ago

      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

  • danextian's avatar
    danextian
    Super User
    1 year ago

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

     

    • ls784's avatar
      ls784
      Helper I
      1 year ago

      Yep! Do you think this is possible?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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&colon;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's avatar
      ls784
      Helper I
      1 year ago

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