Change the currency symbol format for a value column based on another column

[shoaibalvi]

In Power BI desktop, I have a data table which contains a column for values, and another column with currency short code e.g. EUR, GBP etc.

I wish to display the currency symbol in my visualisations which use the value column, and I know one way to achieve this is to select the value column in "data view" and select the appropriate currency symbol from the format pane.

However, the data table will keep changing from time to time, so sometimes the currency symbol might be CAD, on other occasions it could be EUR or AED.

So I am looking for a way that would dynamically change the format of the value column based on what the currency code is in the "document currency" column. I can't convert the values to a string as that would prevent the numbers / totals from displaying in the visualisations.  Any idea how i can achieve the desired result?

[v-shex-msft]

Hi @shoaibalvi ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[shoaibalvi]

Hi @v-shex-msft 

I have looked at the video but haven't been able to quite translate it to what I need at the moment. But I think its just me being stupid and the actual solution might just work and requires a bit more tweaking - I just want to give it another go before I accept the solution. This way I can share precisely what I did differently to make it work for this purpose too 🙂

[Ritaf1983 · Accepted solution]

Hi @shoaibalvi 

You can use dynamic string formatting features to achieve this goal.

Please refer to the linked tutorial:

https://www.youtube.com/watch?v=kiYZxJjcD40

In your scenario, you can use your currency column instead of countries

I prepared pbix with the small sample you can download it and follow my steps

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

[shoaibalvi]

Thanks a lot @Ritaf1983 !

I don't quite have a 1:1 relationship between the sales report and the currency table. This is because the sales report has hundreds of rows - in each row, under the "Document Currency" column there is a currency code like USD, GBP etc. However, this remains unique per sales report so whenever I updates the source data, I will have one unique currency code under "Document Currency" column.

On this basis, I was able to still define a many:many relationship between sales report and a "currency table" that I created, and then took assistance from the pbix file you shared. Seems to work so far!!

Thanks again!