Dynamic Currency Formatting (Comma + Decimal) in Looker Measures — No Working Solution Found

Looker Q&A Looker
, , ,
1

Hi everyone,

I’ve been working on implementing dynamic currency formatting in Looker, where the user can select a currency via a parameter, and the measure should adapt both the currency symbol and the decimal format (0 decimals for JPY, 2 decimals for USD, etc.). However, I’m running into consistent issues where formatting (especially commas/thousands separator) is not applied as expected. I’d like to ask if anyone has found a working solution.

What I Tried

1. Using html + Liquid with number_with_delimiter

measure: test {
  type: sum
  sql: ${daily_price} ;;
  html:
    {% case currency_code._parameter_value %}
    {% when "JPY" %}
    <p style="text-align: right; margin: 0;">¥ {{ value | round: 0 | number_with_delimiter }}</p>
    {% when "USD" %}
    <p style="text-align: right; margin: 0;">$ {{ value | round: 2 | number_with_delimiter }}</p>
    {% when "EUR" %}
    <p style="text-align: right; margin: 0;">€ {{ value | round: 2 | number_with_delimiter }}</p>
    {% else %}
    <p style="text-align: right; margin: 0;">{{ value | round: 2 | number_with_delimiter }}</p>
    {% endcase %}
    ;;
}

Result:

  • Works visually in tables sometimes.

  • But number_with_delimiter often does not render commas for type: sum measures.

  • In certain visualization types and CSV/Excel exports, the output is HTML strings, not numbers.

2. Using value_format

measure: test_jpy {
  type: sum
  sql: ${daily_price} ;;
  value_format: "#,##0"
}

measure: test_thb {
  type: sum
  sql: ${daily_price} ;;
  value_format: "#,##0.00"
}

measure: total_display_string {
  label_from_parameter: currency_code
  type: number
  sql:
    {% if currency_code._parameter_value == "JPY" %} ${test_jpy}
    {% elsif currency_code._parameter_value == "THB" %} ${test_thb}
    {% else %} null {% endif %} ;;
}

3. Using FORMAT() in SQL

measure: test_display_string {
  type: string
  sql: CASE
    WHEN '{% parameter currency_code %}' = 'JPY' THEN CONCAT('¥', FORMAT('%T', CAST(${test} AS INT64)))
    WHEN '{% parameter currency_code %}' = 'USD' THEN CONCAT('$', FORMAT('%T', ${test}))
    WHEN '{% parameter currency_code %}' = 'KRW' THEN CONCAT('₩', FORMAT('%T', CAST(${test} AS INT64)))
    ELSE FORMAT('%T', ${test})
  END ;;
}

Result:

  • Outputs numbers as strings, not numeric fields.

  • Again, no commas in some cases.

Current Situation

  • Goal: Use a parameter to dynamically change currency symbol and decimal places.

  • Problem: None of the above methods provide a consistent solution with commas (thousands separator), numeric export compatibility, and visualization support.

Question

Has anyone successfully implemented dynamic currency formatting (symbol + decimal places + thousands separator) in Looker that works consistently in tables, visualizations, and exports?

Any workaround, best practice, or even confirmation that this is currently not possible would be very helpful.

Thanks in advance!

2

I can tell you that you’re not missing some silver bullet. Getting perfect, dynamic formatting that will work in tables, visualizations, and exports is not currently possible.

As you’ve noticed, each potential solution has a flaw, usually in one of the visualization settings or during the csv/excel export step.

3

Thanks for your reply Sam.
So. Any recommendations?

4

Pick the potential solution that has the flaw you can live with? :sweat_smile:

5

And submit a feature request from within the product to increase visibility.