Solved

Conditional colour change in single value based on percentage

  • 16 February 2021
  • 5 replies
  • 60 views

I’m trying to create a simple KPI dashboard with conditional formatting of a Single Value visualisation, using a comparison, where the formatting (e.g. the background colour) conditions are based on the percentage achievement rather than absolute values.  Is this possible?

 

icon

Best answer by JenniferThomas 19 February 2021, 18:44

A couple things you can do instead. 

  1. Create the % of Target Calculation as a Measure and use that in your single-value viz and use in the Conditional Formatting Option BUT display the integer value instead. For example: 

dimension: target {
    type: number
    sql: 200000 ;;
  }

dimension: target1 {
    type: number
    sql: 1 ;;
  } 
  
  measure: total_order_count {
    type: count_distinct
    sql: ${order_id} ;;
  }
  
  measure: pct_of_target_label_value {
    type: number
    sql: 1.0* ${total_order_count} / ${target} ;;

##use html to display value from total order count instead of calculated percent
    html: {{total_order_count._rendered_value}} ;;
    }

 

You add a target1 and your calculated pct_of_target_label_value fields 

Because the measure is actually a pct (but displayed as original integer value) you can add your comparison without a label. And use the value for conditional formatting. 

 

Now if you want to include the percentage, the simplest way is to add it to your html label like below (you’d have to play a bit with formatting if you want on a separate line). 

measure: pct_of_target_label_value_and_pct {
    type: number
    view_label: "zz Test Metrics"
    sql: 1.0* ${total_order_count} / 200000 ;;
    value_format_name: percent_0
    html: <a style="font-size:24px;"><b>{{total_order_count._rendered_value}}</b></a>
          <a style="font-size:8px;">({{rendered_value}} of target)</a>;;
  }

 

  1. A second option is to build your own progress bar. Here’s an example measure.

measure: count_pct_of_target {
    type: number
    view_label: "zz Test Metrics"
    sql: 1.0* ${total_order_count} / ${target} ;;
    #sql: .50 ;;
    value_format_name: percent_0

#if value over 50% then green else red
    html: {% if value >= 0.50 %}
          {% assign bg_color = "0,180,0" %}
          {% else %}
          {% assign bg_color = "255,0,0" %}
          {% endif %}
          

    <div style="float: left

    ; width:{{ value | times:100}}%

    ; background-color: rgba({{bg_color}},{{ value | times:100 }})

    ; text-align:left

    ; color: #FFFFFF

    ; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 4px;">{{rendered_value}}</p>

    </div>

    <div style="float: left

    ; width:{{ 1| minus:value | times:100}}%

    ; background-color: rgba({{bg_color}},0.1)

    ; text-align:right

    ; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 0px; color:rgba(0,0,0,0.0" )>{{rendered_value}}</p>

    </div>
    
    ;;
  }

When added to your viz it looks like this:

You can also edit to include the integer value too. e.g., where you have {{rendered_value}} above replace with {{total_order_count._rendered_value}} ({{rendered_value}})

 

In addition to the link DMcintosh provided, here are links to other great articles on html conditional formatting (I pulled the second example from the Progress Bars doc):

Progress Bars

Conditional Format Table Cells

Advanced Conditional Formatting

 

Hopefully these gives you some more ideas!

View original

5 replies

Hi Simon,  there doesn’t appear to be a way to do that.  It would certainly be a great feature though that would help quickly identify the health of a KPI.  (Looker product reps should take note of the value of this!)

Userlevel 2

Hi Simon,

Try using conditional formatting to change the Font Color (rather than background color). The color of bar will match the font color. 

 

 

Thanks @JenniferThomas for taking the time to respond.  Unfortunately your suggestion doesn’t quite do what I want.  In your example I’d like the condition statements to be 

 - Green text if more than 50% of target achieved

 - Red text if less than 50% of target achieved

Instead, as you show, I have to write 

 - Green text if more than 125,000

 - Red text if less than 125,000

But my targets change every month, so it might be 300,000 next month, so my colours would be wrong!  I’d like to set the conditions on the percentage not on the absolute values.

 

 

I found this on the subject the other day.  It’s not exactly what you are looking for but its another option you could entertain.

https://help.looker.com/hc/en-us/articles/360023573694-Conditional-Formating-using-Table-Calculations

 

I also saw a thread on this same subject - being able to apply conditional formatting based on another column value (like target) rather than hardcoded values.  The post was from 4 years ago and some Looker reps commented in it saying they were looking at the functionality.  But, that was 4 years ago and it seems like they haven’t addressed it.

Userlevel 2

A couple things you can do instead. 

  1. Create the % of Target Calculation as a Measure and use that in your single-value viz and use in the Conditional Formatting Option BUT display the integer value instead. For example: 

dimension: target {
    type: number
    sql: 200000 ;;
  }

dimension: target1 {
    type: number
    sql: 1 ;;
  } 
  
  measure: total_order_count {
    type: count_distinct
    sql: ${order_id} ;;
  }
  
  measure: pct_of_target_label_value {
    type: number
    sql: 1.0* ${total_order_count} / ${target} ;;

##use html to display value from total order count instead of calculated percent
    html: {{total_order_count._rendered_value}} ;;
    }

 

You add a target1 and your calculated pct_of_target_label_value fields 

Because the measure is actually a pct (but displayed as original integer value) you can add your comparison without a label. And use the value for conditional formatting. 

 

Now if you want to include the percentage, the simplest way is to add it to your html label like below (you’d have to play a bit with formatting if you want on a separate line). 

measure: pct_of_target_label_value_and_pct {
    type: number
    view_label: "zz Test Metrics"
    sql: 1.0* ${total_order_count} / 200000 ;;
    value_format_name: percent_0
    html: <a style="font-size:24px;"><b>{{total_order_count._rendered_value}}</b></a>
          <a style="font-size:8px;">({{rendered_value}} of target)</a>;;
  }

 

  1. A second option is to build your own progress bar. Here’s an example measure.

measure: count_pct_of_target {
    type: number
    view_label: "zz Test Metrics"
    sql: 1.0* ${total_order_count} / ${target} ;;
    #sql: .50 ;;
    value_format_name: percent_0

#if value over 50% then green else red
    html: {% if value >= 0.50 %}
          {% assign bg_color = "0,180,0" %}
          {% else %}
          {% assign bg_color = "255,0,0" %}
          {% endif %}
          

    <div style="float: left

    ; width:{{ value | times:100}}%

    ; background-color: rgba({{bg_color}},{{ value | times:100 }})

    ; text-align:left

    ; color: #FFFFFF

    ; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 4px;">{{rendered_value}}</p>

    </div>

    <div style="float: left

    ; width:{{ 1| minus:value | times:100}}%

    ; background-color: rgba({{bg_color}},0.1)

    ; text-align:right

    ; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 0px; color:rgba(0,0,0,0.0" )>{{rendered_value}}</p>

    </div>
    
    ;;
  }

When added to your viz it looks like this:

You can also edit to include the integer value too. e.g., where you have {{rendered_value}} above replace with {{total_order_count._rendered_value}} ({{rendered_value}})

 

In addition to the link DMcintosh provided, here are links to other great articles on html conditional formatting (I pulled the second example from the Progress Bars doc):

Progress Bars

Conditional Format Table Cells

Advanced Conditional Formatting

 

Hopefully these gives you some more ideas!

Reply