DAX to dynamically change the currency symbol facing issue

[Anonymous]

Hi,
I am trying to dynamically change the currency symbol according to the currency selection in the slicer.
Below is my DAX:

Total Sales Converted =

VAR CurrencySelected = SELECTEDVALUE('Client'[Currency],"USD")

VAR SelectedCurrency = CALCULATE(SUM('Transaction'[Sales Amount]),'Client'[Currency] = CurrencySelected)

VAR AllClientstoUSD = SUM('Transaction'[SalesAmountUSD])

VAR USDFormat = FORMAT(AllClientstoUSD,"$#,##0.00;($#,##0)")

VAR CADFormat = CONCATENATE("CAD ",FORMAT(SelectedCurrency,"$#,##0.00;($#,##0)"))

VAR GBPFormat = FORMAT(SelectedCurrency,"£#,##0.00;($#,##0)")

VAR EURFormat = FORMAT(SelectedCurrency,"€#,##0.00;($#,##0)")

RETURN

SWITCH(

TRUE(),

CurrencySelected = "USD",IF(NOT(ISBLANK(AllClientstoUSD)),USDFormat),

CurrencySelected= "CAD",IF(NOT(ISBLANK(SelectedCurrency)),CADFormat),

CurrencySelected= "GBP",IF(NOT(ISBLANK(SelectedCurrency)),GBPFormat),

CurrencySelected= "EUR",IF(NOT(ISBLANK(SelectedCurrency)),EURFormat),

[Total Sales]

)
The results are correct as shown below:
But the issue I face is I can neither sort it (descending based on Total Sales)
nor can I convert it to a chart as it displays no values for Total Sales

Can you pls help me resolve the issue as to how can I dynamically change the currency symbol according to the currency selection in the slicer and at the same time convert it to a chart and sort the values in a table?

[v-chenwuz-msft]

Hi @Anonymous，

For short ,the problem is that the measure result is string instead of int, so it is blank().

In the bar chart values, you should use a measure to get the value of Total Sales Converted, some measure like the following:

GET VALUE =
VALUE(RIGHT([Total Sales Converted],LEN([Total Sales Converted])-4)) // 4 is the length of "USA" and "$"

Change above measure in your case and I put my pbix you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-chenwuz-msft,
Thank you for replying,
I did my testing and I believe this solution works if I have values like CAD$400 and USD$400. But in my case, I simply have $400 for the US Values and £400 for the British (GBP) Values.

Another issue I see is even if I resolve the above problem, the new calculated measure "GET VALUE" gives me an error when I try to add Client name or Client Id as shown below:

when I add Client Id/ Client Name which is from separate table "Client", the table shows an error

Can you pls help me resolve this?

Lastly, Can you also give me a workaround for sorting the values in a table but with the formatted currency?
as shown in the below screenshot?

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

I have the problem can't display table, my solution is that add a function IF(HASONEVALUE('Table'[ID]), [GET VALUE], total value). However, total value has some diffcultly to calculate. My idea is sum(fact value) * exchange rate.

Back your case, you need a rate. For example , USD 400  when you select CAD the value 400 need multiply 1.24 =497. so , if create a bar chart , (fact values * rate) be the values of the bar chart is a good idea. format measure you created is better when you create a table.

Sort by string is impossible without any other int column. just like your screenshot, add the value column to sort this table.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.