Dynamic Currency Formatting

Anonymous · ‎09-28-2020

Hi

I have a table with sales values for different countries. Is there a way to have dynamic currency formatting based on the country?

For example, if I select Sweden, the values will be in SEK. If I select the UK, the values will be in GBP, etc. I don't want to have this formatted as a text, because these values are used in the charts.

I think I saw a way to have it with Tabular Editor (calculation groups?) but cannot find the thread now...

Anonymous · ‎09-30-2020

HI @Anonymous,

So you mean you want to dynamic convert the current currency values based on current user? If this is a case, you need to create a currency table with different currency rates from a common currency type.

Then you can write a measure formula to extract the currency type from filter and do the conversion of raw table records based on currency table rates. (notice: you can't current dynamic calculated column/table based on filter selections, they not response with filter/slier operations)

BTW, you can also take a look at the following blog about dynamic currency converter:

Building a Dynamic Currency Converter with Power BI

Regards,

Xiaoxin Sheng

Anonymous · ‎09-30-2020

@Anonymous

thank you for the response. what I need is not the currency VALUE, but the currency SYMBOL. I already have a dynamic exchange rate and the value is shown properly based on the country selected. However, I want also this to show the correct currency symbol based on the country selected.

Do you know how to do this?

Anonymous · ‎10-01-2020

HI @Anonymous,

AFAIK, power bi allows you to set table fields with custom display format and keeping raw field types, but it is impossible to use Dax formula/filter to affect/interaction with this custom format of table fields.

Use custom format strings in Power BI Desktop

Perhaps you can consider adding a measure with a switch function to display different currency symbols based on filter effects.

Regards,

Xiaoxin Sheng

Anonymous · ‎10-01-2020

This is correct, I can do this. But it will format the numeric values to strings and will not work with charts.

thank you for your help though.

CNENFRNL · ‎09-29-2020

Hi, @Anonymous , I think you mean this article by Marco Russo?

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous · ‎09-29-2020

Hi @CNENFRNL

Not really. This helps to set up the proper format of the currency symbol (which is what I want), but it is changing the format to string/text (which is something I don't want). Therefore, it will not be working with charts... so will not be the case for me.

thank you for the reply though.