Currency field with symbol becoming string

[ani99]

I have a requirement to show figure in selected currency in filter. It should be a realtime conversion. My data model is capable to support this requirement. I have written below measure which is working as expected but my measure values are appearing as text which should be in currency to support arithmatic operation in filter like greater than, equal , less than etc.





Measure Formula:

TestMeasure4 =
VAR CurrencyCode=SELECTEDVALUE(CurrencyList[isocurrencycode])
VAR ExchangeRate=LOOKUPVALUE(transcurrencies[exchangerate],transcurrencies[isocurrencycode],CurrencyCode)
VAR CurrencySymbol=LOOKUPVALUE(transcurrencies[currencysymbol],transcurrencies[isocurrencycode],CurrencyCode)
VAR FormatStyle=CONCATENATE(CurrencySymbol,"#,##0")
VAR convertedValue = IF((NOT ISBLANK(CurrencyCode) && NOT ISBLANK(ExchangeRate) && NOT ISBLANK(CurrencySymbol)),FORMAT(([TestMeasure1]*ExchangeRate),FormatStyle),FORMAT([TestMeasure1],"$#,##0"))
RETURN convertedValue


Seeking your help. Thanks.

[PaulDBrown]

@ani99 

By the looks of it, your measure is forcing the output as a string (concatenating a symbol with a number for example). By your briefing, you need a numeric value and a specific format based on parameters. If so, what I suggest is to create 2 measures:

1) Number measure: one that delivers a numeric value. Looking at your measure I would guess it's the expression [TestMeasure1]*ExchangeRate. Use this for the calculations and to filter values in the filter pane.

2) Formatted Measure: Format the previous measure to display the required symbol and use this in your visual (it will still be a text string, however, so if you need to include aggregations in your visual, you will have to resort to creating the aggregation using the previous measure, format accordingly, and then use the expression "IF(HASONEVALUE... " to display either the individual value or the aggregation in your visual.

Does that make sense?

[ani99]

Thanks a lot for your reply. That could be a nice work around. But users can export the table and open in excel. I am not sure if I am missing something when I am formatting. 

[PaulDBrown]

@ani99 

Not sure if there is a way of formatting currencies dynamically. What you can do, however, is use the conditional formatting options to display a title to your visual stating the currency displayed, and leave the actual numbers as is - no currency formatting. If users export to a CSV file , the actual file name will be the title itself stating the currency.

Not ideal, but...