Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

FORMAT function not working in line charts

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!

Need Help

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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?

    FORMAT_function_not_working_in_line_charts

     

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Eric,

         

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

         

        Best Regards,

        Dale