FORMAT function not working in line charts

[Anonymous]

Hi everyone!

I´m struggling with the following issue:

I´m creating a report in which the user may choose the display currency for all revenues. I added a currency conversion table to the underlying tabular model, inserted a slicer for the desired currency to the report and created the following revenue measure:

revenue_dynamic_curr:=
FORMAT(
    SUM(example_table[revenue])
        * IF(HASONEVALUE(currency_exchange_table[target_currency]), max(currency_exchange_table[exchange_rate]), 1),

    IF(HASONEVALUE(currency_exchange_table[target_currency]), max(currency_exchange_table[currency_format]), "#,##0 €;(#,##0 €)")
)

As you can see, Eurois the default currency; if another metric is chosen, the respective exchange rate will be applied and the currency sign will change.

This formula works perfectly fine as long as I only use it in tables.

When I try to use it in a line or bar chart, however, nothing happens. I can drag it to the "Values" bin as often as I want to, nothing happens. The issue doesn´t lie in missing relationships or something similar, however, as the measure will work in the "Tooltip" bin when I use another measure in the "Values" bin.

Does anyone else have experience with this and can help me out? I´d appreciate that greatly!

[v-jiascu-msft]

Hi @Anonymous,

That's because the measure return a non-numeric value which can't be added in the "Values" of the line chart. How about use the built-in currency format?

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

thanks for your response! Unfortunately, I can´t make use this functionality because in my specia use case the format is determined by the underlying data source and may not be changed within PowerBI. But even if this was possible, I don´t think that I´d reach my goal of changing the currency format dynamically by doing so since you can only pick one currency at a time. Please correct me if I´m wrong with this.

The solution I´m looking for should allow for a dynamic change from e.g. € to $ etc.

I find it somehow strange that metric whose calculation I posted works in tables and even in tooltips of line and bar charts but not as th "values" of either line or bar chart. Doyou have any further ideas on this?

Best,

Eric

[v-jiascu-msft · Accepted solution]

Hi Eric,

I'm afraid there isn't a good solution for now. Please refer to this post.

Best Regards,

Dale

[Anonymous]

Hi Dale,

it´s not the solution I was hoping for but was already afraid that it might not be possible to do what I intended...

Thanks for your efforts anyway!

Best,

Eric